VBA Select Down to last row in ActiveColumn

[patsfan]

I know this may seem trivial but I am having a difficult time finding a solution.


I use "Range(ActiveCell, [A65535].End(xlUp)).Select" to select everything from activecell down to the last cell in column. Works great if I'm in column A (or any other known column). However, if the column changes, how do I change the "[A65535].End(xlup)" in my code to select the activecolumn?

 

[SirJB7]

Hi, patsfan!


If you accept that your data won't exceed row 65535, you can use this code:

Range(ActiveCell, Cells(65535, ActiveCell.Column).End(xlUp)).Select

so as to avoid fixing column A. This if you don't want to change your code.


Otherwise you should try this, which also works for rows beyond 65535:

-----

[pre]

ActiveCell.Select
Range(Selection, Selection.End(xlDown).End(xlDown).End(xlUp)).Select
-----


Take care that if doesn't work on empty columns, for that you should do something like this (depending on where is your statement included):

-----

ActiveCell.Select
Range(Selection, Selection.End(xlDown).End(xlDown).End(xlUp)).Select
If .Value = "" Then
MsgBox "error"
Else
...do shtng...
End If

[/pre]
-----


Regards!

 

[patsfan]

Thanks Sir

I get a "variable not defined" error with "ActiveColumn" using the suggestion "Range(ActiveCell, Cells(65535, ActiveColumn).End(xlUp)).Select".

The other suggestion... "ActiveCell.End(xlDown).End(xlDown).End(xlUp).Select" only selected the cell in row 65535 BUT this led me to include it inside my code (which I assume you assumed.

This works...Range(ActiveCell, ActiveCell.End(xlDown).End(xlDown).End(xlUp)).Select


Thanks for your help.

 

[SirJB7]

Hi, patsfan!

My mistake, it was "ActiveCell.Column", I've just edited it, and missed the range clause, and edited too.

Just advise if any issue.

Regards!