Check for visible rows after filter

[paddysheeran]

Hi All,

I've used the following code in other workbooks to check for visible rows after a filter is applied. I've not had any problem with it until now but when I run through the code and the filter produces no visible rows of data instead of following the "then" route it carries onto the "else" route. its probably something simple but I cant understand why it's working for one workbook and not another. here is the code:

Sub NON_SLA_Filter()

Sheets("Faults").Select

'Insert filter for faults
Range("B4:O4").AutoFilter
'Filter out SLA Yes faults


Selection.AutoFilter Field:=13, Criteria1:="*No*"
'Check for visible rows
If Cells(1).CurrentRegion.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellT ypeConstants).Count _
- Cells(1).CurrentRegion.Rows(1).Cells.Count = 0 Then
'if there are no visible rows
Range("B4:O4").AutoFilter
Sheets("NON_SLA_FAULTS").Select
Range("B5").Select
ActiveCell.FormulaR1C1 = "Nothing to report for this period"
Exit Sub

Else
'if visible rows exist
Selection.CurrentRegion.Select
Selection.Offset(1, 1).Resize(Selection.Rows.Count - 1, Selection.Columns.Count - 8).Copy

Sheets("NON_SLA_FAULTS").Select
Range("B5").Select
ActiveSheet.Paste
Sheets("Faults").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 8).Resize(Selection.Rows.Count - 1, Selection.Columns.Count - 12).Copy
Sheets("NON_SLA_FAULTS").Select
Range("H5").Select
ActiveSheet.Paste

Sheets("Faults").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 11).Resize(Selection.Rows.Count - 1, Selection.Columns.Count - 13).Copy
Sheets("NON_SLA_FAULTS").Select
Range("J5").Select
ActiveSheet.Paste

Sheets("Faults").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 13).Resize(Selection.Rows.Count - 1, Selection.Columns.Count - 13).Copy
Sheets("NON_SLA_FAULTS").Select
Range("K5").Select
ActiveSheet.Paste
'remove filter
Sheets("Faults").Select
Range("B4:O4").Select
Selection.AutoFilter
End If
End Sub

thanks for looking.

Paddy.

[Bob Phillips]

Doesn't that just mean that there are no visble cells with constants?

[paddysheeran]

possibly - new to vba. i just need it to check whether the are any rows of data below the header once the filter has been applied.

[geekgirlau]

If Cells(1).CurrentRegion.SpecialCells(xlCellTypeVisible).Rows.Count = 1 Then

[Bob Phillips]

possibly - new to vba. i just need it to check whether the are any rows of data below the header once the filter has been applied.

Isn't that what the code does and what you are getting?