1004 Error: No cells were found, easy solution? [d

问题:

This question already has answers here:

Deleting Empty rows in Excel using VBA (5 answers)

Closed 5 years ago.

In my macro, I have a segment which looks at a range, and finds and fills the blank cells.

Range("E10:A" & CStr(bottom - 1)).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Value = "N/A"

where

bottom = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

This works perfectly when a blank cell is present within the range, but throws "1004 Error: No cells were found." at the specialcells.select line. I am having trouble of thinking of an easy way to fix this problem.

I understand that I could run a loop through the range to first check for blank cells, but I feel that this method would be slow and clunky.

Does anyone have a quicker and/or easier solution?

PS I know that I can consolidate my above lines of code, I just laid it out like this here to be easier to understand;

    Range("E10:A" & CStr(bottom - 1)).SpecialCells(xlCellTypeBlanks).Value = "N/A"

回答1:

What you could do to avoid the error popping up is add an error handler

For Example:

Sub Main()
    On Error GoTo NoBlanks
    Range("A1:A10").SpecialCells(xlCellTypeBlanks).Value = "N/A"

NoBlanks:
    Resume Next
    ' or add code here to execute when there are no empty cells
End Sub