Selecting non-blank cells in Excel with VBA

Question

I'm just beginning to dive into VBA and I've hit a bit of a roadblock.

I have a sheet with 50+ columns, 900+ rows of data. I need to reformat about 10 of those columns and stick them in a new workbook.

How do I programmatically select every non-blank cell in a column of book1, run it through some functions, and drop the results in book2?

Impendent

Which program are you using: Excel or Access? Your question title says Excel, but your tags say Access. – Schleswig

Definitely Excel, thanks for pointing that out. This is why you don't tag in a hurry. – Impendent

Answer 1

The following VBA code should get you started. It will copy all of the data in the original workbook to a new workbook, but it will have added 1 to each value, and all blank cells will have been ignored.

Option Explicit

Public Sub exportDataToNewBook()
    Dim rowIndex As Integer
    Dim colIndex As Integer
    Dim dataRange As Range
    Dim thisBook As Workbook
    Dim newBook As Workbook
    Dim newRow As Integer
    Dim temp

    '// set your data range here
    Set dataRange = Sheet1.Range("A1:B100")

    '// create a new workbook
    Set newBook = Excel.Workbooks.Add

    '// loop through the data in book1, one column at a time
    For colIndex = 1 To dataRange.Columns.Count
        newRow = 0
        For rowIndex = 1 To dataRange.Rows.Count
            With dataRange.Cells(rowIndex, colIndex)

            '// ignore empty cells
            If .value <> "" Then
                newRow = newRow + 1
                temp = doSomethingWith(.value)
                newBook.ActiveSheet.Cells(newRow, colIndex).value = temp
                End If

            End With
        Next rowIndex
    Next colIndex
End Sub

Private Function doSomethingWith(aValue)

    '// This is where you would compute a different value
    '// for use in the new workbook
    '// In this example, I simply add one to it.
    aValue = aValue + 1

    doSomethingWith = aValue
End Function

Schleswig

When I try to run this code, I get a message box saying "Object required." – Superintend

Answer 2

I know I'm am very late on this, but here some usefull samples:

'select the used cells in column 3 of worksheet wks
wks.columns(3).SpecialCells(xlCellTypeConstants).Select

or

'change all formulas in col 3 to values
with sheet1.columns(3).SpecialCells(xlCellTypeFormulas)
    .value = .value
end with

To find the last used row in column, never rely on LastCell, which is unreliable (it is not reset after deleting data). Instead, I use someting like

 lngLast = cells(rows.count,3).end(xlUp).row

Hernandez

Answer 3

If you are looking for the last row of a column, use:

Sub SelectFirstColumn()
   SelectEntireColumn (1)
End Sub

Sub SelectSecondColumn()
    SelectEntireColumn (2)
End Sub

Sub SelectEntireColumn(columnNumber)
    Dim LastRow
    Sheets("sheet1").Select
    LastRow = ActiveSheet.Columns(columnNumber).SpecialCells(xlLastCell).Row

    ActiveSheet.Range(Cells(1, columnNumber), Cells(LastRow, columnNumber)).Select
End Sub

Other commands you will need to get familiar with are copy and paste commands:

Sub CopyOneToTwo()
    SelectEntireColumn (1)
    Selection.Copy

    Sheets("sheet1").Select
    ActiveSheet.Range("B1").PasteSpecial Paste:=xlPasteValues
End Sub

Finally, you can reference worksheets in other workbooks by using the following syntax:

Dim book2
Set book2 = Workbooks.Open("C:\book2.xls")
book2.Worksheets("sheet1")

Peacemaker

Answer 4

For me the best way to proceed was to:

1. Create a new Excel Table
2. AutoFilter it by the parameter Criterial:="<>"

An example of the code would be:

Sub ExampleFilterCol()
    ' Create a Table
    Dim ws As Worksheet
    Dim rg As Range
    Set ws = ActiveSheet
    Set rg = ws.Range("A1").CurrentRegion
    ws.ListObjects.Add(xlSrcRange, rg, , xlYes).Name = "myNonRepeatedTableName"

    ' Filter the created table
    Dim Io As ListObject
    Dim iCol As Long
    ' Set reference to the first Table on the sheet 
    ' That should be the recently created one
    Set lo = Sheets("Totalinfo").ListObjects(1)
    ' Set filter field
    iCol = lo.ListColumns("yourColumnNameToFilter").Index
    ' Non-blank cells – use NOT operator <>
    lo.Range.AutoFilter Field:=iCol, Criteria1:="<>"
End Sub

Dibromide

Answer 5

This might be completely off base, but can't you just copy the whole column into a new spreadsheet and then sort the column? I'm assuming that you don't need to maintain the order integrity.

Kudu