Continue to the next item in a for each loop if auofilter returns no results for current object in collection

[dpaton05]

I have a with statement nested within a for each loop. What is the syntax to continue on to the next item in the for each loop from within the with statement. I have tried to insert the continue statement after checking the count of the visible rows=1 (the header) but that doesn't work.

My code is

        For Each ws In wb2.Worksheets
                If ws.Name <> "Cancellations" And ws.Name <> "Totals" And ws.Name <> "Sheet2" Then
                        With ws.[A3].CurrentRegion
                                'On Error Resume Next
                                'Autofilter the late cancel date enter in B34 with dates in column 1
                                .AutoFilter 1, LCDt
                                'Autofilter the late cancel request number with request numbers in column 3
                                .AutoFilter 3, LCReq
                                'Add the service to a varaible
                                'Service = .Areas(1).Cells(2, 5).Value
                                
                                
                                
                                'Add code for filter to check number of
                                AutoFilterCounter = .Columns(1).SpecialCells(xlCellTypeVisible).Count
                                    'If value is 1, only the heading is visible so skip to the next sheet.
                                    If AutoFilterCounter = 1 Then
                                        Continue
                                
                                    
                                    
                                    With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
                                    
                                        Service = .Areas(1).Cells(2, 5).Value
                                    End With

                                With Data
                                    .Cells(30, 1) = LCDt
                                    .Cells(30, 2) = Service
                                    .Cells(30, 5) = 3
                                    .Cells(30, 6) = 1
                                End With
                                
                                LCPrice = Data.Cells(30, 8).Value
                                
                                    'With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
                                        .Areas(1).Cells(2, 8).Value = LCPrice
                                        .Areas(1).Cells(2, 9).Formula = "=IF(RC[-4]=""Activities"",0,RC[-1]*0.1)"
                                        .Areas(1).Cells(2, 10).Formula = "=RC[-1]+RC[-2]"
                                    'End With
                                
                              
                                .AutoFilter
                        End With
                End If
        Next ws

 

[Peter_SSs]

Instead of trying to skip if AutoFilterCounter = 1, just try to do what you want if AutoFilterCounter > 1

That is something like this.

With ws.[A3].CurrentRegion

  'Some code here
  
  If AutoFilterCounter > 1 Then

    'All the rest of what you want to happen if visible rows > 1
  
  End If
End With

.

 

[dpaton05]

Haven't had a chance to try it yet but I was wondering, would it be better to have an if or a while?

 

[Peter_SSs]

.. would it be better to have an if or a while?

No

 

[dpaton05]

Thanks Peter

 

[Peter_SSs]

You're welcome.