Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Range Selection (Excel 2002)

    Hi,
    The following portion of my macro works awesome on selecting data that has more than one line of data AFTER the first row (A9:Z9). I have this part of the code in a loop, and several of the worksheets I'm consolidating only have one line of data, so because of the "Range(Selection, Selection.End(xlDown)).Select" part of the code it then selects all the rows down to 65536. I can't figure out how to change the code so that it will select my data no matter how many rows of data are in each worksheet. Any ideas?
    Thanks so much!!
    Lana

    Range("A9:Z9").Select
    Range(Selection, Selection.End(xlDown)).Select

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Range Selection (Excel 2002)

    If there are no other data below the block of cells you need, you can also use

    Dim lngLastRow As Long
    lngLastRow = Range("A:Z").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A9:Z" & lngLastRow).Select ' or whatever you want to do with this range

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Range Selection (Excel 2002)

    <P ID="edit" class=small>(Edited by Rudi on 09-Jan-08 10:41. Modified to add a link to a useful thread...)</P>The CurrentRegion property should work well. It stops selecting when it encounters an entirely blank row or column.

    Try modifying the code to: Range("A9").CurrentRegion.Select

    PS: A secret of VBA code is that you do not need to select the cells to do something with it. So for example: If you need to fill the last cell with a value of 10, you do not need:

    Range("A1").End(xlDown).Select
    Activecell.Value = 10

    You can simply type:

    Range("A1").End(xlDown).Value = 10

    ========================
    This thread starting at <post#=683,649>post 683,649</post#> shows some useful techniques to calculate the range object, store it in a variable and then use the range variable to do what is needed in the spreadsheet. If you browse through it, it may give you some more insight into how to work with range references...
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •