Code to fill blank cells UP not Down for group of records 1

[jdttek]

Groups of imported records have name of account on subtotal line only. Need to fill blank account name cells for each record in group. Have been able to fill down from header but not up from subtotal.
e.g.:

Need to get ABC to fill UP in column A for each group of records.
Example:

A B C
xxx xxxx
yyy yyyy
zzz zzzz
---- ----- ----
ABC xyz xxyyzz

A B C
ABC xxx xxxx
ABC yyy yyyy
ABC zzz zzzz
---- ----- ----
ABC xyz xxyyzz


Following code works fine to fill DOWN if acct name is on TOP of group as a header, but I can't get the reverse to work (filling UP) if acct name (ABC)is on bottom as subtotal, even if I change the offset to (1,0).


Sub FillUp()
For Selection
If Trim(Cell)="" And Cell.Row>1 Then
Cell.Value = Cell.offset(-1,0).value
End If
Next Cell
End Sub


Any help greatly appreciated

JDTTEK

 

[xlbo]

Try this
For i = range("A65536&quot.end(xlup).row to 2 step -1
If range("A" & i).text = "" then
range("A" & i).value = range("A" & i+1).value
else
end if
next i

HTH
Geoff

 

[jdttek]

Thanks Geoff
I will this today and let you know how it works.

What does "2 step -1" do?

 

[xlbo]

Translates to For i = big number to small number step -1

ie go from the biggest number to the smallest number in steps of 1 (increment downwards). If you leave out the step -1, it will finish the loop on the 1st attempt 'cos it will default to step 1 (increment upwards)

Can use pretty much any step value.....-10, 20, 100, whatever you want to increment in
HTh
Geoff

 

[jdttek]

Geoff
Works like a charm. Thanks.

I was not aware of the -1 step argument option in the For..Next statement.

Is there any way to acomplish the same results (i.e reverse order processing)with a Do statement or with the For...Next without having to start at the last cell. (i.e. any way to vary the range based on the number of records each time it is run?)

Also, on ur solution, why did u use ".end(xlup)" Seems to work ok with just Range("A65536&quot.row.

 

[xlbo]

Not sure about Dos and For Eachs with a negative increment but the
= range("A65536&quot.end(xlup).row bit is to start at the last cell with data in it. It's equivalent to going to A65536 and pressing end then up so it does in fact start at the last cell with data and is dynamic. This I think answers both your queries
Rgds
Geoff