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")