View original (French)

Variable Range Selection

Dror -  
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 answers

  1. 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
    1. pijaku Moderator
      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
    2. michel_m Contributor
      @pijakuHello 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
    3. pijaku Moderator
      @michel_mHi 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
  2. michel_m Contributor
    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
    1. Dror
      Whoa! Absolutely.

      And where is dernlign?
      What is the msgbox for?
      0
  3. Theo.R
    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