PDA

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



aLUPIN
03-08-2017, 06:28 AM
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
03-08-2017, 07:02 AM
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
03-08-2017, 08:51 AM
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! :clap::clap::clap:

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?
:crying:


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

jolivanes
03-08-2017, 09:40 AM
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
03-09-2017, 06:17 AM
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
03-09-2017, 06:52 AM
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... :confused:
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 (https://en.wikipedia.org/wiki/COBOL), FORTRAN (https://en.wikipedia.org/wiki/FORTRAN) and PL/1 (https://en.wikipedia.org/wiki/PL/1) manuals of the 1960s.[8] (https://en.wikipedia.org/wiki/Magic_number_(programming)#cite_note-MartinG25-8) The use of unnamed magic numbers in code obscures the developers' intent in choosing that number,[9] (https://en.wikipedia.org/wiki/Magic_number_(programming)#cite_note-MartinG16-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] (https://en.wikipedia.org/wiki/Magic_number_(programming)#cite_note-10) Replacing all significant magic numbers with named constants (https://en.wikipedia.org/wiki/Constant_(programming)) makes programs easier to read, understand and maintain.[11] (https://en.wikipedia.org/wiki/Magic_number_(programming)#cite_note-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
03-09-2017, 07:53 AM
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.