Filling up instead of down (2007)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

Perfect as usual Hans! I was making it too complicated. Thanks for pointing me to this simpler solution.

Reply | Quote