View original (French)

Variable Range Selection

Dror -  
pijaku Posted messages 13513 Registration date   Status Modérateur 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

3 réponses

Réponse 1 / 3
michel_m Posted messages 18903 Registration date   Status Contributeur Last intervention   3 320
 
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
0
Dror
 
Whoa! Absolutely.

And where is dernlign?
What is the msgbox for?
0
Réponse 2 / 3
Theo.R Posted messages 585 Status Membre 31
 
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?
-1
Réponse 3 / 3
Dror
 
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.
-2
pijaku Posted messages 13513 Registration date   Status Modérateur Last intervention   2 771
 
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
0
michel_m Posted messages 18903 Registration date   Status Contributeur Last intervention   3 320 > pijaku Posted messages 13513 Registration date   Status Modérateur Last intervention  
 
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
0
pijaku Posted messages 13513 Registration date   Status Modérateur Last intervention   2 771 > michel_m Posted messages 18903 Registration date   Status Contributeur Last intervention  
 
Hi Michel,

Yes, I know this code is not reliable, I've created a practical sheet on this topic...
I just reiterated what the requester stated.
However, my response here is only for educational purposes.

But, otherwise, you are correct about the find method: it's the only reliable one.

See you!
0