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

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

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

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

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

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

Examples related to vba

Examples related to excel

Examples related to spreadsheet