Variable Range Selection
Dror
-
pijaku Posted messages 13513 Registration date Status Moderator Last intervention -
pijaku Posted messages 13513 Registration date Status Moderator Last intervention -
Hello everyone,
Configuration: Windows XP / Internet Explorer 8.0
Please help, I want to select a range downwards, starting from the active cell, up to a row that I have memorized in "dernlign".
I'm stuck.
I have this
Range(Selection, Selection.End(xlDown)).Select
but I want to stop it at dernlign.
Thank you
Configuration: Windows XP / Internet Explorer 8.0
Please help, I want to select a range downwards, starting from the active cell, up to a row that I have memorized in "dernlign".
I'm stuck.
I have this
Range(Selection, Selection.End(xlDown)).Select
but I want to stop it at dernlign.
Thank you
3 answers
-
Hello
Dim range As Range, start As String, end As String
start = ActiveCell.Address
end = Columns(ActiveCell.Column).Find(what:="*", searchdirection:=xlPrevious).Address
Set range = Range(start & ":" & end)
MsgBox range.Address 'check
--
Michel -
Hello,
I'm not quite sure I understood: do you want to select the range between the active cell going down to the cell in "dernlign"?
If so, you just need a simple Range(...).. could you please provide us with your entire code to identify the active cell as simply as possible? -
First, to identify the last row of the table, I do:
Range("B5").Select
Selection.End(xlDown).Select
dernlign = Selection.Row
Then, later, I find myself on some cell, and I need to select a range starting from that cell, going down to the stored row.
Usually, I do:
Range("G6:G" & dernlign).Select
But there, I might not necessarily be in G.
So I did:
Range(Selection, Selection.End(xlDown)).Select
But it goes all the way down.-
Try this test, it will show you what a MsgBox is...
Sub test() MsgBox Selection.Address MsgBox Split(Selection.Address, "$")(1) Dim Lettre As String, dernLign As Long, PremLign As Integer PremLign = 6 dernLign = Range("B5").End(xlDown).Row Lettre = Split(Selection.Address, "$")(1) MsgBox Range(Lettre & PremLign & ":" & Lettre & dernLign).Address End Sub- Hello Frank
lastLign = Range("B5").End(xlDown).Row
weird thread because apparently unreliable:
if there is an empty cell before the last row, the returned row will be that of that cell
- according to AV, MVP*Excel, the only reliable one is the one I use based on his advice.
I'm sending you the workbook (using the "evaluate" function) via DM
As for Dror, I thank him for his politeness and particularly open-mindedness...- MVP: Most valuable Professional, a title awarded by the parent company Microsoft and given annually
-
