Range maneuvers question

  • Thread starter Thread starter CellShocked
  • Start date Start date
C

CellShocked

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.
 
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
 
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.
 
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]
 
This will clear the sheet execpt row 1.

Sub sl()
Dim x As String
x = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address
ActiveSheet.Range("A2:" & x).ClearContents
End Sub
 
It would have helped had you mentioned about clearing the contents in your
original post!!

JLGWhiz has given you a solution
 
This will clear the sheet execpt row 1.

Sub sl()
Dim x As String
x = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address
ActiveSheet.Range("A2:" & x).ClearContents
End Sub
Click to expand...

You Da Man!

Go Chargers!
 
I guess that you have reading issues.

What other reasons would one want to select an entire sheet's contents,
and why would you get so miffed over it?

Is there a pissing contest to see which of you can help first or
something?

Jeez... raise your maturity level above that of a ten year old, dude.

In other words, excuse the f*ck out of me.

Thanks, guys for the help. That works fine. Thanks for yours too,
issues boy. :-)

As far as the rest goes... I guess nobody is familiar with anything 2010
here then.
 
The error 400 occurs when you try to open a UserForm that is already loaded
and displayed. So, if you are getting that error when you open workbook,
you need to check the ThisWorkbook code module to see if there is a
Workbook_Open event code that is calling a UserForm that might already be
open. If not, then you need to try and isolate the cause that initiates the
error and report it for a possible bug in the 2010 beta version.
 
The error 400 occurs when you try to open a UserForm that is already loaded
and displayed. So, if you are getting that error when you open workbook,
you need to check the ThisWorkbook code module to see if there is a
Workbook_Open event code that is calling a UserForm that might already be
open. If not, then you need to try and isolate the cause that initiates the
error and report it for a possible bug in the 2010 beta version.
Click to expand...


No, I am certain that it is in my macro script.

Thanks. I need to trap for the condition of the workbook, and act
accordingly. Thanks again.
 
Back
Top