Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filling up instead of down (2007)

    I am working on a project where I have been given a worksheet that was originally subtotals that have been pasted as values into another sheet. My task is to fill the values in columns B to E with the subtotal values. I was trying to write a loop that would start at the last value in the column and work its way up. I know I want it to look at this criteria, but I am not sure how to put it into VBA.

    If B,row = "Grand" delete entire row
    If B,Row has a value, check to see if the next row up has a value
    If it does, then move to the next row, if it does not then copy the paste the value into the new cell.
    I have this code started, but then I got lost.

    r = Range("B" & Rows.Count).End(xlUp).Row

    For n = r To 1 Step -1

    If (Cells(n, 2) = "Grand") _
    Then
    Cells(n, 1).EntireRow.Delete
    Else
    If (Cells(n - 1, 2) > 0) _
    Then

    Else
    Cells(n, 2).Copy
    Cells(n - 1, 2).Paste
    End If
    Next n
    End Sub

    Any help would be appreciated. I have attached a sample of the data.
    Attached Files Attached Files
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

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

    Re: Filling up instead of down (2007)

    Does this do what you want?

    Sub FillErUp()
    Dim r As Long
    Dim m As Long
    Dim c As Long
    m = Cells(Rows.Count, 2).End(xlUp).Row
    If Cells(m, 2) = "Grand" Then
    Rows(m).Delete
    m = m - 1
    End If
    For r = m To 2 Step -1
    For c = 2 To 5
    If Cells(r - 1, c) = "" Then
    Cells(r - 1, c) = Cells(r, c)
    End If
    Next c
    Next r
    End Sub

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling up instead of down (2007)

    Perfect as usual Hans! I was making it too complicated. Thanks for pointing me to this simpler solution.
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

Posting Permissions

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