Solved: Copy down to blank cells?

[nat1]

Hi,

I have this code to copy the previous row of data to blank cells:Public Sub Copydown()
Dim i As Long
Dim iLastRow As Long
Dim iStart As Long

With ActiveSheet

iLastRow = Range("H" & Rows.Count).End(xlUp).Row
iStart = 1
For i = 2 To iLastRow
If Range("A" & i).Value <> "" Then
If i > iStart + 1 Then
Range("A" & iStart).AutoFill Range("A" & iStart).Resize(i - iStart)
End If
iStart = i
End If
Next i
End With

End Sub
The problem is when the last populated cell in col A is reached the macro stops, even though in col H there maybe data several rows beyond that of col A.

How can I modify this code to copy down the values in col A to the last row in col H?

Thank-you

[figment]

try this.

Public Sub Copydown()
Dim i As Long
Dim iStart As Long
iStart = 1
For i = 3 To Range("H" & Rows.Count).End(xlUp).Row
If Range("A" & i).Value <> "" Or Range("H" & i).Value <> "" Then
If i > iStart + 1 Then
Range("A" & iStart).AutoFill Range("A" & iStart).Resize(i - iStart)
End If
iStart = i
End If
Next
End Sub

[nat1]

figment,

Thank-you for your response. I tried this code but it is giving me the same result.

The last populated cell in col A is not copied down to the last populated cell in H. For example A2567 is the last cell in col A and H2650 is the last in col H. So I need the value from A2567 copied down to A2650.

All cells before A2567 are copied down with no probs.

Thanks for your help.

[figment]

ok try replacing

For i = 3 To Range("H" & Rows.Count).End(xlUp).Row

with

For i = 3 To Range("H" & Rows.Count).End(xlUp).Row+1

[nat1]

figment,

Thanks for the reply, I tried the revised code but it is still giving me the same result.

Any other suggestions? I do appreciate your time and efforts.

Thanks again.

[david000]

Range("a1:h" & Cells(Rows.Count, "h").End(xlUp).Row).FillDown < "H" being the longest column.

Assumes H is the longer col.

I'm seeing your example code say's ("A" <> "") not equal to blank means there is something at the end of the column.
Which in this case there is not.

[nat1]

david000,

Thanks for your suggestion. I'm assuming I must have done something wrong, because this code actually clears the values A1:H.

Any ideas?

Thanks

[figment]

i have been trying to figure out why you use If i > iStart +1 ? as far as i can tell this just results in a bunch of gaps. and if the last value falls in the row right befor the last row, then iStart +1 will result in the code skipping that last row in fact the more i think about it it should force the code to skip over most long areas of nothing. i would sugest trying i > iStart or i >= istart+1
,unless you wanted the code to skip long periods of nothing. if you could through up a sample workbook showing what the input is and what you want the output to be then i could be of farther help.

[nat1]

figment,

Thanks for your reply. I have attached a sample file for you to look at.

I would also like to copy down the values in col G also. Would it be possible to include this column aswell?

Regards

[figment]

this might be a bit slow but its simple

Sub testing()
Dim a As Long
a = 2
With Worksheets("4C.tmp")
While .Range("H" & a) <> ""
If .Range("A" & a) = "" Then .Range("A" & a) = .Range("A" & a - 1)
If .Range("G" & a) = "" Then .Range("G" & a) = .Range("G" & a - 1)
a = a + 1
Wend
End With
End Sub

[nat1]

Thank-you so much, that's just perfect!! Thanks for helping me out.

Regards,