Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Moving a Range (2000/2003)

    Using code how do you move a range A4:O7 to the first empty row then move all the rows up so you don't have the 4 empty rows left.
    I tried doing this in a macro but the first empty row will not be the same all the time and I don't know how to account for this.

    Thanks

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

    Re: Moving a Range (2000/2003)

    Assuming that the cell in column A is populated for every non-empty row:

    Dim r As Long
    r = Range("A65536").End(xlUp).Row + 1
    Range("A4:O7").Cut
    Range("A" & r).Insert Shift:=xlDown

    If you don't know which cell will be populated in the last non-empty row, compute r as follows (the rest of the code remains the same)

    r = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving a Range (2000/2003)

    Hans,
    I get an error on the line:
    Range("A" & r).Cut

    1004 Method 'Range' of object '_ Global' failed
    I get that message with either r= statement
    Scott

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving a Range (2000/2003)

    Hans,
    Nevermind, I saw the typo

    Thanks as always,
    Scott

Posting Permissions

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