Easy VB question from newbie.

dmagnus

Board Regular
Joined
Dec 16, 2005
Messages
66
I am using the following line of code to go to the last row/column of some data in excel:

ActiveCell.SpecialCells(xlLastCell).Select

In this example, my active cell ends up being X135, though on some other sheets on which this macro will be run it could be a different row & column. All I want to do now is move the activecell to the first column of this last row - in this example, move to A135.

I think this should be easy, but I'm new and my hours of searching have come up blank. Any suggestions? Thanks, Dan
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,912
Office Version
  1. 365
Platform
  1. Windows
Dan

You could perhaps use something like this.
Code:
Range("A" & ActiveCell.SpecialCells(xlLastCell).Row).Select
PS Why are you selecting anyway?
 

dmagnus

Board Regular
Joined
Dec 16, 2005
Messages
66
I'm trying to find the last row of the data because I need to put three new lines of test beginning at the row just beneath the last row of the data.

I'll give your suggestion a try. Thanks.
 

dmagnus

Board Regular
Joined
Dec 16, 2005
Messages
66
That code worked great. Thanks for the help. A side question, now.

The data for which I'm trying to find the last row is actually a Business Objects report(s) that I've saved as Excel. The code you provided actually takes me about 4 rows beneath the last row of data, which makes me think that for whatever reason there are 4 rows of data "observed" by Excel but no visible (probably a B.O. issue).

If I can't find a way to correct this B.O. issue, is there a way to move-up the activecell a couple of rows? I'm somewhat familiar with "offset" but don't know how/where to put it with your code.

Thanks!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,912
Office Version
  1. 365
Platform
  1. Windows
Dan

Well I don't think you actually need to select anything to do that.:)

You hardly ever need to select/activate ranges/worksheets etc.

Not 100% sure what you are trying to do but without selecting this will place test in the 3 cells below the last cell in column A.
Code:
Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(3) = "test"
 

Watch MrExcel Video

Forum statistics

Threads
1,113,812
Messages
5,544,458
Members
410,613
Latest member
Texman
Top