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?

Answer 1

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