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

User · Answer

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

User · Answer

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  lt  gt     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

User · Answer

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

User · Answer

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

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

Examples related to vba

Examples related to excel

Examples related to spreadsheet