Count blank cells to avoid run-time error 1004

psi

New Member
Joined
Sep 16, 2012
Messages
5
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:

Code:
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

Legend
Joined
Jun 19, 2002
Messages
63,651
Try

Rich (BB code):
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board.

Try

Code:
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
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
scrap this ... my answer was wrong :(

So was mine, CountA should be changed to CountBlank
Code:
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

Board Regular
Joined
Oct 17, 2009
Messages
57
So was mine, ...
I wrote this one:
Code:
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

New Member
Joined
Sep 16, 2012
Messages
5
Try

Rich (BB code):
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

New Member
Joined
Sep 16, 2012
Messages
5
Code:
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!
 

Forum statistics

Threads
1,086,033
Messages
5,387,396
Members
402,059
Latest member
wdyl121

Some videos you may like

This Week's Hot Topics

Top