How to check if the next visible cell has anything in it

[dpaton05]

I have some code that filters rows and if there is nothing found by the filter, it skips to the next sheet. How do I make this line of code

 If ws.[A3].Cells.Offset(1, 0) = "" Then

refer to the next filtered, visible row as at the moment, it looks at the next row, filtered or not?

        For Each ws In Worksheets
                If ws.Name <> "Cancellations" And ws.Name <> "Totals" And ws.Name <> "Sheet2" Then
                        With ws.[A3].CurrentRegion
                                .AutoFilter 1, Dt           ' autofilter for the value in cell [B27]
                                .AutoFilter 3, Req          ' autofilter for the value in cell [B25]
                                    'Check to see if the date cell, column A, for a job has anything in it. If it doesn't, turn the autofilter off and skip to the next sheet.
                                    If ws.[A3].Cells.Offset(1, 0) = "" Then
                                        .AutoFilter
                                       
                                        GoTo SkipSheet
                                    End If

I get a type mismatch with this

If ws.[A3].Cells.Offset(1, 0).SpecialCells(xlCellTypeVisible) = "" Then

 

[Michael M]

I don't quite follow but shouldn't it be....

If ws.[A3].Offset(1, 0) <> "" Then

 

[dpaton05]

If the autofilter, filters out all the results, the next visible row below row 3 (header row) will be empty so skip to the next sheet I was thinking.

 

[Akuini]

Try:

                                    'Check to see if the date cell, column A, for a job has anything in it. If it doesn't, turn the autofilter off and skip to the next sheet.
                                    If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then
                                        .AutoFilter
                                       
                                        GoTo SkipSheet
                                    End If

 

[dpaton05]

That gives me an overflow error of this line

AutoFilterCounter = .Columns(1).SpecialCells(xlCellTypeVisible).Count

in this code

Sub Transfer()
        Dim ws As Worksheet, sh As Worksheet, sht As Worksheet, AutoFilterCounter As Long
        Set sh = Sheets("Totals")
        Set sht = Sheets("Cancellations")
        Dim Req As String: Req = sh.[B25].Value
        Dim Dt As String: Dt = sh.[B27].Value
        
Call TurnOffFunctionality
        
        For Each ws In Worksheets
                If ws.Name <> "Cancellations" And ws.Name <> "Totals" And ws.Name <> "Sheet2" Then
                        With ws.[A3].CurrentRegion
                                .AutoFilter 1, Dt           ' autofilter for the value in cell [B27]
                                .AutoFilter 3, Req          ' autofilter for the value in cell [B25]
                                    'Check to see if the date cell, column A, for a job has anything in it. If it doesn't, turn the autofilter off and skip to the next sheet.
                                    If ws.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then
                                        .AutoFilter
                                        GoTo SkipSheet
                                    End If
                                    
                                'Autofilter counter = number of visible rows
                                AutoFilterCounter = .Columns(1).SpecialCells(xlCellTypeVisible).Count
                                    'If value less than 2, only the heading is visible so skip to the next sheet.
                                    If AutoFilterCounter < 2 Then
                                        .AutoFilter
                                        GoTo SkipSheet
                                    End If
                                
                                .Offset(1).EntireRow.Copy sht.Range("A" & Rows.Count).End(xlUp).Offset(1)
                                .Offset(1).EntireRow.Delete
                                .AutoFilter                 ' turn off the autofilter
                        End With
                End If
SkipSheet:
        Next ws
        
'sh.Range("B25,B27").ClearContents
Call TurnOnFunctionality

End Sub

 

[dpaton05]

Can someone please help me with this problem?