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?
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

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,089,606
Messages
5,409,250
Members
403,256
Latest member
Viq

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top