Selecting non-blank cells in Excel with VBA Selecting non-blank cells in Excel with VBA vba vba

Selecting non-blank cells in Excel with VBA


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

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

or

'change all formulas in col 3 to valueswith sheet1.columns(3).SpecialCells(xlCellTypeFormulas)    .value = .valueend 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


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 ExplicitPublic 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 colIndexEnd 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 = aValueEnd Function


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

Sub SelectFirstColumn()   SelectEntireColumn (1)End SubSub SelectSecondColumn()    SelectEntireColumn (2)End SubSub SelectEntireColumn(columnNumber)    Dim LastRow    Sheets("sheet1").Select    LastRow = ActiveSheet.Columns(columnNumber).SpecialCells(xlLastCell).Row    ActiveSheet.Range(Cells(1, columnNumber), Cells(LastRow, columnNumber)).SelectEnd 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:=xlPasteValuesEnd Sub

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

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