CODEWITHSUNDEEP
vbaexcel
vivek padelkar
vivek padelkar

Reputation: 313

getting 1004 error "No cells were found "when i find no cells after filter in VBA

My problem is when I filter the records I get sometimes result as a no cells but im getting error as 1004 "no cells were found " i also used goto error method but it fails to perform.

I used bellow code 

  If Error Then GoTo NoBlanks
           vfast_narecord = xlwkbOutput.Sheets("Sheet1").Range("F2:F" & lastRow).SpecialCells(xlCellTypeVisible).Cells.Count
On Error Resume Next
NoBlanks:
MsgBox "No cells were found!"
    Resume Next

Upvotes: 0

Views: 1413

Answers (2)

Slai
Slai

Reputation: 22866

Few other options to your previous question that don't need filtering and counting visible cells:

  • =AGGREGATE(3,3,A:A) Excel function to count the number of cells in column A that are not empty, error, hidden, or SUBTOTAL / AGGREGATE function
  • =Count(A:A) to count only cells containing numbers (including hidden cells but ignores errors)
  • =CountIf(A:A,#N/A) to count cells containing #N/A error

All of those should be available with WorksheetFunction in VBA

Upvotes: 0

arcadeprecinct
arcadeprecinct

Reputation: 3777

The syntax is

On Error GoTo NoBlanks

To disable the handler, use 

On Error GoTo 0

as using On Error Resume Next will cause it to ignore all subsequent errors.

You also need to put your handler behind an Exit Sub/Exit Function or it will be executed every time. For example

    '...
    Exit Sub
NoBlanks:
    '...
    Resume Next
End Sub

Upvotes: 1

Related Questions