range: from active cell to last cell in current column (discontinuing)

[aLUPIN]

Hi Folks

I'm looking for the solution to the following.
I want to select a range/selection that is from the active (selected) cell to the last (used) cell in that column (discontining range!)
So I'm in need of something like:

Range(ActiveCell, Range("D1048576").End(xlUp)).Select

where D should be replaced by the Column(letter) of the active cell.

Unable to find something decent on that...
Anyone? Thx.

[Paul_Hossler]

I try to avoid hard coding things is there's another way


Someome might wonder where the 'Magic Number' 1048576 came from

Sub Select1()
    With ActiveCell.Parent
        Range(ActiveCell, .Cells(.Rows.Count, ActiveCell.Column).End(xlUp)).Select
    End With
End Sub

[aLUPIN]

I try to avoid hard coding things is there's another way

Someome might wonder where the 'Magic Number' 1048576 came from

Sub Select1()
    With ActiveCell.Parent
        Range(ActiveCell, .Cells(.Rows.Count, ActiveCell.Column).End(xlUp)).Select
    End With
End Sub

Hey Paul,
That's was exactly what I was looking for!
Thank you very much!
This will make my work so much easier again!

However, maybe a small question/adaptation.
What if another column in the sheet has data in a row that is beyond (greater number) than the one of the current one?
How would the formula look then?

Range(ActiveCell, .Cells(.Rows.Count, ActiveCell.Column).End(xlUp)).Select

I guess ActiveCell.Column should then be replaced by the 'longest' column?
or in other words:
the range should be from the ActiveCell down to the lowest used row in the sheet.

Did I state this clear?

Someone might wonder where the 'Magic Number' 1048576 came from...
Do you really wonder? For your info:
Excel2007 and higher support (2^20 = 1048576) rows and (2^14 = 16384, FXD) columns

[jolivanes]

Maybe

Sub Select2()
Dim lr As Long
lr = Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
 '----> In the above line, you might need to change "xlValues" to "xlFormulas"
    With ActiveCell.Parent
        Range(ActiveCell, .Cells(lr, ActiveCell.Column)).Select
    End With
End Sub

[aLUPIN]

Maybe

Sub Select2()
Dim lr As Long
lr = Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
 '----> In the above line, you might need to change "xlValues" to "xlFormulas"
    With ActiveCell.Parent
        Range(ActiveCell, .Cells(lr, ActiveCell.Column)).Select
    End With
End Sub

Works like a charm!
Thank you!

[Paul_Hossler]

Original requirement:

I want to select a range/selection that is from the active (selected) cell to the last (used) cell in that column

Revised requirement:

What if another column in the sheet has data in a row that is beyond (greater number) than the one of the current one?

jolivanes' works for that also

Someone might wonder where the 'Magic Number' 1048576 came from...
Do you really wonder? For your info:
Excel2007 and higher support (2^20 = 1048576) rows and (2^14 = 16384, FXD) columns

Sorry, I slipped into jargon-speak. A 'Magic Number' is a number that just appears in a macro without any obvious basis

https://en.wikipedia.org/wiki/Magic_number_(programming)

The term magic number or magic constant also refers to the programming practice of using numbers directly in source code. This has been referred to as breaking one of the oldest rules of programming, dating back to the COBOL, FORTRAN and PL/1 manuals of the 1960s.^[8] The use of unnamed magic numbers in code obscures the developers' intent in choosing that number,^[9] increases opportunities for subtle errors (e.g. is every digit correct in 3.14159265358979323846 and is this equal to 3.14159?) and makes it more difficult for the program to be adapted and extended in the future.^[10] Replacing all significant magic numbers with named constants makes programs easier to read, understand and maintain.^[11]
Names chosen to be meaningful in the context of the program can result in code that is more easily understood by a maintainer who is not the original

So using Activesheet.Rows.Count instead of 1048576 or 2^20 is (IMHO at least) clearer, as well as working without change in older pre-2007 Excel. Hard coding 1048576 would break if ever used in a pre-2007 workbook (and there are still lots of those around)

[aLUPIN]

Thanks for the info

I must be wise, knowing I don't know a lot...
Thanks for your reply; this helped me out a lot.