help with 'no cells found' error... (ideas on how to get around this...)

[kbishop94]

my code cycles through many several auto-filtering routines in order to capture an individual metric before moving on to capture the next one. the metrics are captured from a worksheet that contains data for employee turnover (including when someone starts, when someone leaves, the department they are/were in and the facility where they did/do work. )

when it encounters an autofilter combination where it wasnt able to return any rows that contain anything, this is where I run into a problem:



normally I would just stick in a "on error resume next" command so it doesnt get tripped up and instead just moves on to the next sequence (the next autofilter routine in the code.) this does prevent encountering an error.

but, in this case, I cant do that because of what the code does when it encounters the situation where no rows (cells) where found. (instead of leaving the cell blank like it should when no data for that month was found, it instead is sticking in a "-1". :


heres a snippet of the code so you can see what its doing: (i shortened it but it continues on for many more dates (each month) .. back to 2019.)

With ws
    If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
        ActiveSheet.Range(Cells(4, 1), Cells(sCol, 26)).Rows.Hidden = False
        ActiveSheet.Range(Cells(4, 1), Cells(sCol, 26)).AutoFilter Field:=4, Criteria1:="REMOVED"
        ActiveSheet.Range(Cells(4, 1), Cells(sCol, 26)).AutoFilter Field:=6, Criteria1:="Customer Service"
        ActiveSheet.Range(Cells(4, 1), Cells(sCol, 26)).AutoFilter Field:=8, Criteria1:="El Campo"
End With
'*********************************************************
Dim Jan24_Y: Dim Feb24_Y: Dim Mar24_Y: Dim Apr24_Y
Dim May24_Y: Dim Jun24_Y: Dim Jul24_Y: Dim Aug24_Y
Dim Sep24_Y: Dim Oct24_Y: Dim Nov24_Y: Dim Dec24_Y
'***********
Jan24_Y = 0: Feb24_Y = 0: Mar24_Y = 0: Apr24_Y = 0
May24_Y = 0: Jun24_Y = 0: Jul24_Y = 0: Aug24_Y = 0
Sep24_Y = 0: Oct24_Y = 0: Nov24_Y = 0: Dec24_Y = 0
'*********************************************************
For Each yP In Range(Cells(4, 15), Cells(sCol, 15)).SpecialCells(xlCellTypeVisible)
    If yP.value = "Apr-2024" Then
        Apr24_Y = Apr24_Y + 1
        Worksheets("STATS").Cells(94, 169).value = Apr24_Y
        Else
    End If
    If yP.value = "Mar-2024" Then
        Mar24_Y = Mar24_Y + 1
        Worksheets("STATS").Cells(93, 169).value = Mar24_Y
        Else
    End If
    If yP.value = "Feb-2024" Then
        Feb24_Y = Feb24_Y + 1
        Worksheets("STATS").Cells(92, 169).value = Feb24_Y
        Else
    End If
    If yP.value = "Jan-2024" Then
        Jan24_Y = Jan24_Y + 1
        Worksheets("STATS").Cells(91, 169).value = Jan24_Y
        Else
    End If
    If yP.value = "Dec-2023" Then
        Dec23_Y = Dec23_Y + 1
        Worksheets("STATS").Cells(90, 169).value = Dec23_Y
        Else
    End If
    If yP.value = "Nov-2023" Then
        Nov23_Y = Nov23_Y + 1
        Worksheets("STATS").Cells(89, 169).value = Nov23_Y
        Else
    End If
    If yP.value = "Oct-2023" Then
        Oct23_Y = Oct23_Y + 1
        Worksheets("STATS").Cells(88, 169).value = Oct23_Y
        Else
    End If
    If yP.value = "Sep-2023" Then
        Sep23_Y = Sep23_Y + 1
        Worksheets("STATS").Cells(87, 169).value = Sep23_Y
        Else
    End If
Next yP
'***********
Worksheets("CHANGE_LOG").Activate
With ws
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
End With
'

and here is the error that it runs into: (the error only happens when I dont have the 'on error resume next' inserted... which I cant have because of the '1's that it inserts into each cell when there arent any visible rows found...(which the 2nd picture from the top shows.)



what I was TRYING to do and get to work was a line of code that before the "For Each" line it CHECKS to see if there are any visible rows below Row 4 and if there isn't (which would be the case if the autofilter wasnt able to find any data/rows), then skip (else) that entire section and jump down to the next autofilter routine.

Any ideas on how to get around this? Thanks

 

[Alex Blakenburg]

Would something like this work for you ?

Sub VisibleCellsOnError()

    Dim rng As Range
    Dim tmpErr As Long
    Dim yP As Range
    
    On Error Resume Next
    Set rng = Nothing
    Set rng = Range(Cells(4, 15), Cells(sCol, 15)).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    If Not rng Is Nothing Then
     For Each yP In rng
     
         ' the rest of your code
         Debug.Print yP, " ", yP.Address
     
     Next yP
    End If
End Sub

 

[kbishop94]

Would something like this work for you ?

Sub VisibleCellsOnError()

    Dim rng As Range
    Dim tmpErr As Long
    Dim yP As Range
   
    On Error Resume Next
    Set rng = Nothing
    Set rng = Range(Cells(4, 15), Cells(sCol, 15)).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
   
    If Not rng Is Nothing Then
     For Each yP In rng
    
         ' the rest of your code
         Debug.Print yP, " ", yP.Address
    
     Next yP
    End If
End Sub

Yes sir it does!

Thank you, that is an excellent solution and will work for me in other situations as well!

 

[Alex Blakenburg]

Glad to hear that. Thank you.