To select the last cell, regardless of any blank cells in the range,
select the range and use

With Selection
.Cells(.Cells.Count).Select
End With

To select the last cell with data in it, use

With Selection
.Cells.SpecialCells(xlCellTypeConstants). _
SpecialCells(xlCellTypeLastCell).Select
End With

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]






On Sun, 03 Jan 2010 14:51:45 -0800, CellShocked
<cellshocked@thecellvalueattheendofthespreadsheet. org wrote:

I want to highlight the entire sheet (minus the header row),
and clear the contents just before a refill with updated data.

Unless there is a query (of sorts) that only replaces changed data or
adds new data.

That would actually be the right way to go, but I fear that a 180k
record sheet might be a while performing that act.

So, I simply kill the original data, and paste in the current
replacement.

No insight on the error problem, eh?

Thanks for your help though.

I think all I need is a select command, but I also want to exclude the
header row. I could simply replace it as well I suppose though.

Here it is, if you are high bandwidth and like you DVD collection.

It is a bit kludgey as I never used any dialogs to speak of, and no
error trapping routines anywhere.


On Sun, 3 Jan 2010 14:30:01 -0800, Mike H
wrote:

Hi,

To select the last populated cell in a column use

Cells(Cells.Rows.Count, "A").End(xlUp).Select

One caveat is that it is highly unlikely you actually need to select the
cell to do what you want.

Mike

"CellShocked" wrote:


Is there a standard command in a macro that will move one to the bottom
of a range?

The row count varies, and I always want to get to the bottom, and
recording a macro doesn't work as it uses direct references.

Also, for some reason, ever since I opened this workbook in 2010 beta,
I get a 400 error in my sheet manipulation routine. It never happened in
2007, but now even happens in that release. Did opening change something
about the workbook that I do not know about?

It used to work fine.

I do think that I have a very recent, pre-2010 backup, however. I
haven't tried to see if that one does it as well. If it does, does that
make it a registry problem, since my 2010 install was told to keep the
current version intact? Both are 32 bit. The 2007 is pro, and the 2010
is a beta offering I DLd from MS.
.