Selecting non-blank cells in Excel with VBA

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?

From stackoverflow

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

• 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
  

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

• 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.

  Tyler Rash : That is completely off base.

• 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 = range("c65000").end(xlUp).row