Determine the last non-blank cell in a range

[Aaron Dyck]

I have a range (called rng), and I need to determine the row number that the
last non-blank cell in this range refers to. I have the following code:

set rng=thisworkbook.sheets(1).range("A1:A65535")

There should be no non-blanks in the sheet between the first entry (in A1)
and the last entry (which I am trying to find). I need to set my counter
variable, z, to the number of records in the range.

Any suggestions?

 

[Dave Peterson]

msgbox application.counta(rng)

would tell you how many cells in rng have something in them.

If you wanted the lastrow that had something in it, you could use something
like:

dim LastRow as long
with thisworkbook.sheets(1)
lastrow = .cells(.rows.count,"A").end(xlup).row
end with