[RESOLVED] Seleting specific range

[lilmark]

I am using Excel 2007.

It seems so simple, but I can't piece together the last line of code. I'm drawing a blank for some reason.

'Stores the number of the last row
Range("G1").Select
Selection.End(xlDown).Select
intLastRow = ActiveCell.Row

'Stores the number of the last column
Range("G1").Select
Selection.End(xlToRight).Select
intLastColumn = ActiveCell.Column

What I'm trying to do is select the entire group range. I tried using...

Range("G1", ????).select

The problem here is if I use intLastColumn it'll give me the # of the column (Ex. A = 1, B = 2, C = 3, etc). I need the letter so I can put...

"P" & intLastRow

...in orde to select the entire group. Is there a way to Convert the number back to the column letter or even store the Column letter rather than the #?

[Siddharth Rout]

Your answer lies in this link...

http://www.vbforums.com/showpost.php...98&postcount=2

[lilmark]

Are you sure it's in there?

Let me add a little more depth before referring to the link again as I was unable to apply any of it to what I needed.

The number of columns & rows changes every month. And, the first column and first row are the only ones that are always populated with data. The cells in the middle are sometimes blank. That is why I was targetting the initial G1 cell and doing the xlToRight & xlDown to store the last row/column.

That's why when I stored the intLastColumn I was hoping I could convert it back to the column letter rather than the number or figure out how to store the Letter rather than the Number.

If the answer is still in the link I'll look again, but at first look I didn't see anything I could use in this situation.


Here's an example.....

[Siddharth Rout]

Do you want the range G1:L7 ?

[lilmark]

Yes, I do. But I have to keep in mind that the number of rows & columns can change every month. And, the cells in the middle don't always have data just as in the example.

That's why in the example I provided I was storing the last row/column so I could trying to select the entire group by entering...

Range("G1", ????).Select

[Siddharth Rout]

Ok will the first row and the first column have data always? if yes then this will help you...

Sub GetRangeAddress()
    Range(Range("D7"), Range("D7").End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    MsgBox Selection.Address
End Sub

[lilmark]

Figured how to select the entire group where the rows/columns aren't always constant...

    Dim intLastRow As Integer

    Range("G1").Select
    Selection.End(xlDown).Select
    intLastRow = ActiveCell.Row
    Range("G1").Select
    Selection.End(xlToRight).Select
    Range("G1", ActiveCell.Offset(intLastRow - 1, 0)).Select

Wish you could store the column letter rather than the number of columns.

[Siddharth Rout]

Figured how to select the entire group where the rows/columns aren't always constant...

Not even the 1st row and 1st column?