Repetition problem with On Error Goto xx

[KAMAL60]

Sponsored Links

I ask my macro to select a data range visible after a filter in multiple tabs. If there is no beach visible (which can happen), I put an On Error Goto to pass it to the next tab. This works if the error appears only once, but not both ...

Here are simplified in my line of code:

    Sheets("A").select 
    Range("A1").Select 
    On Error GoTo Suite1 
    With Range("_FilterDatabase") 
    Set plagefiltervisible = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible) 
    With plagefiltervisible.Areas(1).Rows(1).Select 
    Range(Selection, Selection.End(xlDown)).Select 
    End With 
    End With 
Suite1: 
    Sheets("B").select 
    Range("A1").Select 
    On Error GoTo Suite2 
    With Range("_FilterDatabase") 
    Set plagefiltervisible = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible) 
    With plagefiltervisible.Areas(1).Rows(1).Select 
    Range(Selection, Selection.End(xlDown)).Select 
    End With 
    End With 
Suite2: 
    Sheets("C").select 
    Range("A1").Select 
    On Error GoTo Suite3 
    With Range("_FilterDatabase") 
    Set plagefiltervisible = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible) 
    With plagefiltervisible.Areas(1).Rows(1).Select 
    Range(Selection, Selection.End(xlDown)).Select 
    End With 
    End With 
Suite3: 
    Sheets("D").select 
    Range("A1").Select 
    On Error GoTo Suite4 
    With Range("_FilterDatabase") 
    Set plagefiltervisible = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible) 
    With plagefiltervisible.Areas(1).Rows(1).Select 
    Range(Selection, Selection.End(xlDown)).Select 
    End With 
    End With 
Suite4: 
End sub

If the leaf B and C in the sheet is not visible in data, the code blocks on the line marked in BLUE.

[Zecho]

Have you tried to Err.Clear after each label suite1: suite2: etc. ...
?

suite1:
Err.Clear

Also I have always seen On Error to be associated with Resume

[KAMAL60]

I just tried with Err.Clear and it makes me exactly the same thing. I do not understand why this does not work twice. If the error happens once, it leaves much to good result.

[!const]

Here is a solution that probably works, although there probably is way to simple:

   Sheets("A").select  
    Range("A1").Select  
    On Error GoTo Err1 
    With Range("_FilterDatabase" )  
    Set plagefiltervisible = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible)  
    With plagefiltervisible.Areas(1).Rows(1).Select  
    Range(Selection, Selection.End(xlDown)).Select  
    End With  
    End With  
Main_suite: 
    Go To Suite4 
Err1:  
    Sheets("B" ).select  
    Range("A1" ).Select  
    On Error GoTo Err2 
    With Range("_FilterDatabase" )  
    Set plagefiltervisible = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible)  
    With plagefiltervisible.Areas(1).Rows(1).Select  
    Range(Selection, Selection.End(xlDown)).Select  
    End With  
    End With  
Err1_fin: 
    Resume Main_suite 
Err2:  
    Sheets("C" ).select  
    Range("A1" ).Select  
    On Error GoTo Err3  
    With Range("_FilterDatabase" )  
    Set plagefiltervisible = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible)  
    With plagefiltervisible.Areas(1).Rows(1).Select  
    Range(Selection, Selection.End(xlDown)).Select  
    End With  
    End With  
Err2_fin: 
    Resume Err1_fin 
Err3:  
    Sheets("D" ).select  
    Range("A1" ).Select  
    On Error GoTo Err3_fin 
    With Range("_FilterDatabase" )  
    Set plagefiltervisible = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible)  
    With plagefiltervisible.Areas(1).Rows(1).Select  
    Range(Selection, Selection.End(xlDown)).Select  
    End With  
    End With  
Err3_fin: 
    Resume Err2_fin 
Suite4:  
End sub