VBA cell range selection query.

[Justin C]

Excel 2004

I have a spreadsheet 5500 rows by 11 columns. It's not a static size, it
grows a little most days. I need to select a range, A1 to the very last
used cell in J.

Now, in VBA I can get to the last cell in J by:
Range("J5").End(xlDown)

But I don't know how to put what that cell is into a variable so I can
use it. I can get the contents of the cell, but that's not what I want.

I looked at setting my range using:
UsedRange.Rows.Count

but there are some blank rows after the end of the data that are also
selected (I don't know how these come about, there's something the user
is doing that introduces these from time to time, but I can't figure out
what, so there is nothing I can do about them).

How do I get VBA to tell me which cell is active? I don't mind if it's
Cell(5500,10) format or J5500, I'm sure I can figure things from there.

Thank you for any help you can give with this.

Justin.

 

[Bob Greenblatt]

Excel 2004

I have a spreadsheet 5500 rows by 11 columns. It's not a static size, it
grows a little most days. I need to select a range, A1 to the very last
used cell in J.

Now, in VBA I can get to the last cell in J by:
Range("J5").End(xlDown)

But I don't know how to put what that cell is into a variable so I can
use it. I can get the contents of the cell, but that's not what I want.

I looked at setting my range using:
UsedRange.Rows.Count

but there are some blank rows after the end of the data that are also
selected (I don't know how these come about, there's something the user
is doing that introduces these from time to time, but I can't figure out
what, so there is nothing I can do about them).

How do I get VBA to tell me which cell is active? I don't mind if it's
Cell(5500,10) format or J5500, I'm sure I can figure things from there.

Thank you for any help you can give with this.

Justin.

Perhaps the user is entering blanks, and old Lotus trick to erase cells.
This causes your range to extend beyond where you think it is. To get the
number you want, use: range("j5").end(xldown).row

 

[Justin C]

How do I get VBA to tell me which cell is active? I don't mind if it's
Cell(5500,10) format or J5500, I'm sure I can figure things from there.

OK, following up myself.

I found the solution, and for those who know as little as I do about VBA
and were wondering, here it is:

myRow = ActiveCell.Row
myCol = ActiveCell.Column

I've not found another problem (isn't there always more than one?) for
which I will start another thread.

Justin.