none
Selecting Cell ranges using variables RRS feed

  • Question

  • I have a large report that extracts data points and presents it to a chart for distribution. I've got most of the subroutine working but I'm slightly confusing myself on how to use the Range(Selection, Selection ... properly with my current set of variables.

    I have a piece of code that sorts a column and then finds the first blank cell in the column. It then stores the row value of that blank cell in: intBlank

    I have another piece of code that determines the last row in my data extract sheet and stores the row value in: intLastDataRow

    The worksheet & table names are both "Data" which is stored in: strData

    What I want to do is select all the data from a range based off my variables. From there I can move / copy / etc as needed.

    So, if I sort my column and the first blank row ends up at row 3421, that becomes intBlank. I am able to select the appropriate cell i want to start a selection from by using:

    Sheets(strData).Range("X" & intBlank).Select

    This would put my active cell at X3421.

    From there I want to select all the data in columns X, Y, Z from row 3421 to the "last data row", which ends up around 8800+ rows. To select my range, I have been trying to use: 

    Sheets(strData).Range("X" & intBlank).Select
    Range(Selection, Selection.End("Z" & intLastDataRow)).Select
    
    

    My thinking is that it should start at X3421 and select through Z8800, but it's not. I've also tried:

    Sheets(strData).Range("X" & intBlank).Select
    Range(Selection, Selection.End(intLastDataRow)).Select
    Range(Selection, Selection.End(xlToRight).Offset(3, 0)).Select


    Which isn't doing it for me either... ugh.. Can anyone shed some light on the proper use of Range(Selection and variables.

    =

    Thanks in advance!


    I'm new, please help. Begging for forgiveness in advance.
    Saturday, October 15, 2011 2:20 PM

Answers

  • Sub RangeRover()
    Dim r As Range
    Set r = Range("X3421")
    Dim LastRow As Long
    LastRow = 88800
    r.Select
    Range(Selection, Cells(LastRow, "Z")).Select
    MsgBox Selection.Address
    End Sub

    gsnu201109
    • Marked as answer by JMStumpf Saturday, October 15, 2011 3:05 PM
    Saturday, October 15, 2011 2:48 PM
    Moderator

All replies

  • Sub RangeRover()
    Dim r As Range
    Set r = Range("X3421")
    Dim LastRow As Long
    LastRow = 88800
    r.Select
    Range(Selection, Cells(LastRow, "Z")).Select
    MsgBox Selection.Address
    End Sub

    gsnu201109
    • Marked as answer by JMStumpf Saturday, October 15, 2011 3:05 PM
    Saturday, October 15, 2011 2:48 PM
    Moderator
  • Oh you are the man Gary! Cells was the key..

    Using: 

    Sheets(strData).Range("X" & intBlank).Select

    Range(Selection, Cells(intLastDataRow, "Z")).Select

    Worked like a charm. Thanks!


    I'm new, please help. Begging for forgiveness in advance.
    Saturday, October 15, 2011 3:05 PM