Error with macro to fill blank rows with data from above (ti

[Anjo]

I used this macro (tip 13 from Mr Excel)


Public Sub CopyDown() LastRow = Range("A65536").End(xlUp).Row For i = 1 To LastRow If Range("A" & i).Value = "" Then Range("A" & i - 1 & ":CB" & i - 1).Copy Destination:=Range("A" & i) Next iEnd Sub

I've got the message that "End If" was missing and afterwards I got an error message "1004"

Please can someone help me the solve this problem.

[James]

Do you have the syntax: "End IF"

"Your Code"
End If
End Sub

As your last line of code above End Sub?

James

[Anjo]

Dear James,

this was the simple part of the problem.
I still had the problem with error 1004

[James]

Can you post "all" of the code and show
us the line thats returning the error meaasge?

James

[Anjo]

Public Sub CopyDown()
LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
If Range("A" & i).Value = "" Then
Range("A" & i - 1 & ":CB" & i - 1).Copy Destination:=Range("A" & i)
End If
Next i
End Sub

The line with causes the problem:
"Range("A" & i - 1 & ":CB" & i - 1).Copy Destination:=Range("A" & i)"


See mrExcel archives tip 13


[ This Message was edited by: Anjo on 2002-04-27 13:35 ]

[Ivan F Moala]

On 2002-04-27 13:03, Anjo wrote:
Public Sub CopyDown()
LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
If Range("A" & i).Value = "" Then
Range("A" & i - 1 & ":CB" & i - 1).Copy Destination:=Range("A" & i)
End If
Next i
End Sub

The line with causes the problem:
"Range("A" & i - 1 & ":CB" & i - 1).Copy Destination:=Range("A" & i)"


See mrExcel archives tip 13


[ This Message was edited by: Anjo on 2002-04-27 13:35 ]

The code worked for me IF
1) I had data in column A
2) I had a blank cell within that column

??

[Anjo]

when I change the code
"If Range("A" & i).Value = "" Then" and I put in "0" instead of ""
and also put in 0 in de blank cells it works perfect.
It seems it doesn't see the blank cells

[Ivan F Moala]

On 2002-04-27 15:39, Anjo wrote:
when I change the code
"If Range("A" & i).Value = "" Then" and I put in "0" instead of ""
and also put in 0 in de blank cells it works perfect.
It seems it doesn't see the blank cells

The cells may NOT be Blank but contain a non
printable/vieable character

You can test this by place in a cell
=CODE(D14) where D14 houses your data
If cells display a number then you have data
in those cells.

[Anjo]

Dear James and Ivan,

thank you very much for your support.
The problem was that there the first row may not be empty.

Thanks

from the Netherlands

Anjo