Selecting non-blank cells in Excel with VBA

vba excel spreadsheet
Tyler Rash picture Tyler Rash · May 4, 2009 · Viewed 104.9k times · Source

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

Patrick Honorez picture Patrick Honorez · Sep 26, 2009

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

Related questions

Importing Excel spreadsheet data into another Excel spreadsheet containing VBA

We need to write an Excel spreadsheet with VBA code in it; the code reads and performs operations on the data in the first worksheet. The user will be receiving spreadsheets containing data but that do not contain the VBA …

Read More
What is the difference between Sheets.Select and Sheets.Activate?

In VBA for Excel, what is the difference between Sheets.Select and Sheets.Activate ?

Read More
Excel VBA code to select non empty cells

In Excel, let's I have data in B2 to B7 and C2 to C7 . In VBA I can write a macro to select it: Sub Macro1() Range("B2:C7").Select End Sub How do I rewrite the code so that …

Read More