Count blank cells to avoid run-time error 1004

[psi]

I got a macro connected to a button that select empty cells within a range and fill them with the value above. The code looks like this:

Range("A1:A10").SpecialCells(xlCellTypeConstants, 23).Select
Selection.Resize(, 4).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"

Range("A1:A10").SpecialCells(xlCellTypeConstants, 23).Select
Selection.Resize(, 4).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

If you press the button twice you will get run-time error 1004 becuase Excel can't find any empty cells.

I want a function that counts blank cells before running the macro. If there are no blanks I don't want to execute the macro. That is to avoid run-time error 1004.

Can you guys help me out?

 

[VoG]

Try

Range("A1:A10").SpecialCells(xlCellTypeConstants, 23).Select
Selection.Resize(, 4).Select
If WorksheetFunction.CountBlank(Selection) = 0 Then Exit Sub
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"

Range("A1:A10").SpecialCells(xlCellTypeConstants, 23).Select
Selection.Resize(, 4).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

 

[Jonmo1]

Welcome to the board.

Try

Sub test()
With Range("A1:A10").SpecialCells(xlCellTypeConstants, 23).Resize(, 4)
    If Application.CountA(.Cells) > 0 Then
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End If
End With
End Sub

 

[mozhils]

scrap this ... my answer was wrong

 

[Jonmo1]

scrap this ... my answer was wrong

So was mine, CountA should be changed to CountBlank

Sub test()
With Range("A1:A10").SpecialCells(xlCellTypeConstants, 23).Resize(, 4)
    If Application.CountBlank(.Cells) > 0 Then
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End If
End With
End Sub

 

[mozhils]

So was mine, ...

I wrote this one:

Range("A1:A10").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Value = ActiveCell.Offset(-1, 0).Value

but all it did was input the value of the first "one cell up" in all the empty spaces. And in the end - the same 1004 on the second press

 

[psi]

Try

Range("A1:A10").SpecialCells(xlCellTypeConstants, 23).Select
Selection.Resize(, 4).Select
If WorksheetFunction.CountBlank(Selection) = 0 Then Exit Sub
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"

Range("A1:A10").SpecialCells(xlCellTypeConstants, 23).Select
Selection.Resize(, 4).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

That did the trick!

I had something similar going on but I messed it up somewhere.

Thanks for all the replies guys! Much appreciated!

 

[psi]

Sub test()
With Range("A1:A10").SpecialCells(xlCellTypeConstants, 23).Resize(, 4)
    If Application.CountBlank(.Cells) > 0 Then
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End If
End With
End Sub

It seems like your code is better than my original code. I think I might use it instead. Thanks!