By Stefan

2019-01-11 11:59:34 8 Comments

Below code is part of a larger code where some data is filtered by using AutoFilter. And in most instances there is data in the filtered cells. However, in some instances there is no data, and when trying to use copy, I get the error "No Cells". Which makes sense.

Therefore I'm trying to use below code to exit the sub, if the filtered cells has no data in them. But the "No Cells" error keeps popping up in the first line of the code.

I've tested the range and it's populated correctly.

I've tried .Cells.Count and just .Count. Nothing works.

Can someone help me?

If .Range("C5:C" & lastrow).SpecialCells(xlCellTypeVisible).Cells.Count = 0 Then
    .AutoFilterMode = False
    Exit Sub
End If


@Siddharth Rout 2019-01-11 12:09:21

Whenever you are working with SpecialCells, sandwich them betweeen OERN and OEGTO as shown below

Is this what you are trying? (Untested)

On Error Resume Next
Set rng = .Range("C5:C" & lastrow).SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If rng Is Nothing Then
    .AutoFilterMode = False
    Exit Sub
End If

@Stefan 2019-01-11 13:14:53

Yes this accomplished what I needed, thanks :)

Related Questions

Sponsored Content

3 Answered Questions

[SOLVED] Nested For loops and filtered data

2 Answered Questions

Excel Macro Autofilter and Delete Rows

1 Answered Questions

[SOLVED] Filtering Excel with VBA and exiting Sub if there is no result

1 Answered Questions

[SOLVED] Using VBA Autofilter to exclude all values between two numbers

1 Answered Questions

VBA - Filtered list to ComboBox with multiple columns

0 Answered Questions

Excel AutoFilter between two user defined inputs

1 Answered Questions

[SOLVED] CountIf and xlCellTypeVisible producing 1004 error

1 Answered Questions

[SOLVED] Find the next visible row

  • 2014-02-06 03:54:14
  • Bosch Man
  • 2735 View
  • 1 Score
  • 1 Answer
  • Tags:   excel excel-vba vba

3 Answered Questions

[SOLVED] Error 1400: there is no such cell that satisfies criterias

Sponsored Content