Repetition problem with On Error Goto xx
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:
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.
Re: Repetition problem with On Error Goto xx
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
Re: Repetition problem with On Error Goto xx
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.
Re: Repetition problem with On Error Goto xx
Here is a solution that probably works, although there probably is way to simple:
Code:
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