multiple error handlers

S

Steeviee

Active Member
Joined
Sep 9, 2009
Messages
380
hello Gang,

I have some code that filters a list of data, then highlights the data and selects the visible cells only. If there are no lines that fit the data, then I get an error message...

Code: 
On Error GoTo lnlab
    Rows(x1 - 1).Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=24, Criteria1:="<>"
    Rows(x1 & ":" & x2).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Delete Shift:=xlUp
lnlab:
   Selection.AutoFilter Field:=24
      Selection.AutoFilter

This works fine to get rid of the filter and carry on with the code. However, I have the above type of code applied to 5 areas of the page (defined by x1 & x2). If I put an error handler (similar to 'inlab' above) at the end of each section, it only works for the first error. If I just use one error handler and put it at the end of the code, it just goes to end sub.

I'm sure there is a way to do this? The error handler can be just the one or I can have 6 of them, which ever works.

I hope I've explained this OK. Looking forward to some help as always.

Cheers,
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Steeviee,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Rich (BB code): 
On Error GoTo lnlab
    Rows(x1 - 1).Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=24, Criteria1:="<>"
    Rows(x1 & ":" & x2).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Delete Shift:=xlUp
lnlab:
  
  On Error GoTo 0
  
   Selection.AutoFilter Field:=24
      Selection.AutoFilter
 
Upvote 0
Thanks for thinking about this - I am still having trouble though with the second pass through.

Where should I put the error handling code?

Here's what I have just now:

Code: 
On Error GoTo lnlab
    Rows(x1 - 1).Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=23, Criteria1:="<>"
    Rows(x1 & ":" & x2).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Delete Shift:=xlUp
lnlab:
    On Error GoTo 0
   Selection.AutoFilter Field:=23
      Selection.AutoFilter
y1 = Application.WorksheetFunction.Match("BIF", Worksheets("Rev (rep)").Range("A:A"), 0) + 1
z1 = Application.WorksheetFunction.Match("M/C", Worksheets("Rev (rep)").Range("A:A"), 0) + 1
y2 = z1 - 3
On Error GoTo lnlab2
    Rows(y1 - 1).Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=23, Criteria1:="<>"
    Rows(y1 & ":" & y2).Select
    [COLOR=green][B][U]Selection.SpecialCells(xlCellTypeVisible).Select
[/U][/B][/COLOR]    Selection.Delete Shift:=xlUp
lnlab2:
On Error GoTo 0
    Selection.AutoFilter Field:=23
    Selection.AutoFilter

It goes through the first pass, goes to 'inlab', takes off the filter. then it goes to the second pass - with the new range to filter. It then gives me an error message at the highlighted part because there are no visible cells. Why doesn't it go to the error handler?

Your help is much appreciated.
 
Upvote 0
Steeviee,

Without having your workbook to test the code it is difficult to solve.

If you can send me your workbook for testing:

See my Private Message to you (top right hand corner of MrExcel, Welcome, Steeviee., Private Messages:
 
Upvote 0
Steeviee,

Thanks for the workbook.

The Error routines do not work based on the format/style of worksheet Rev (rep).

Instead of Error routines I used code(s) similar to this:

Code: 
MyCnt = Evaluate(Application.Subtotal(103, Range("X" & z1 & ":X" & z2)))
If MyCnt < 1 Then
'do nothing
Else
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
End If

Before the macro, the last row is 239. After the macro, the last row is 236.



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Sub RevRep()
' hiker95, 05/05/2011
' http://www.mrexcel.com/forum/showthread.php?t=548020
'
Dim x1 As Integer
Dim x2 As Integer
Dim y1 As Integer
Dim y2 As Integer
Dim z1 As Integer
Dim z2 As Integer
Dim p1 As Integer
Dim p2 As Integer
Dim q As Integer
Dim MyCnt As Long, LR As Long
Application.ScreenUpdating = False
x1 = 3
y1 = Application.WorksheetFunction.Match("BIF", Worksheets("Rev (rep)").Range("A:A"), 0) + 1
x2 = y1 - 3
z1 = Application.WorksheetFunction.Match("M/C", Worksheets("Rev (rep)").Range("A:A"), 0) + 1
y2 = z1 - 3
p1 = Application.WorksheetFunction.Match("Assy&Test", Worksheets("Rev (rep)").Range("A:A"), 0) + 1
z2 = p1 - 3
q = Application.WorksheetFunction.Match("Del", Worksheets("Rev (rep)").Range("A:A"), 0) + 1
p2 = q - 3
Rows(x1 - 1).Select
Selection.AutoFilter
Selection.AutoFilter Field:=24, Criteria1:="<>"
Rows(x1 & ":" & x2).Select
MyCnt = Evaluate(Application.Subtotal(103, Range("X" & x1 & ":X" & x2)))
If MyCnt < 1 Then
  'do nothing
Else
  Selection.SpecialCells(xlCellTypeVisible).Select
  Selection.Delete Shift:=xlUp
End If
Selection.AutoFilter Field:=24
Selection.AutoFilter
y1 = Application.WorksheetFunction.Match("BIF", Worksheets("Rev (rep)").Range("A:A"), 0) + 1
z1 = Application.WorksheetFunction.Match("M/C", Worksheets("Rev (rep)").Range("A:A"), 0) + 1
y2 = z1 - 3
Rows(y1 - 1).Select
Selection.AutoFilter
Selection.AutoFilter Field:=24, Criteria1:="<>"
Rows(y1 & ":" & y2).Select
MyCnt = Evaluate(Application.Subtotal(103, Range("X" & y1 & ":X" & y2)))
If MyCnt < 1 Then
  'do nothing
Else
  Selection.SpecialCells(xlCellTypeVisible).Select
  Selection.Delete Shift:=xlUp
End If
Selection.AutoFilter Field:=24
Selection.AutoFilter
z1 = Application.WorksheetFunction.Match("M/C", Worksheets("Rev (rep)").Range("A:A"), 0) + 1
p1 = Application.WorksheetFunction.Match("Assy&Test", Worksheets("Rev (rep)").Range("A:A"), 0) + 1
z2 = p1 - 3
Rows(z1 - 1).Select
Selection.AutoFilter
Selection.AutoFilter Field:=24, Criteria1:="<>"
Rows(z1 & ":" & z2).Select
MyCnt = Evaluate(Application.Subtotal(103, Range("X" & z1 & ":X" & z2)))
If MyCnt < 1 Then
  'do nothing
Else
  Selection.SpecialCells(xlCellTypeVisible).Select
  Selection.Delete Shift:=xlUp
End If
Selection.AutoFilter Field:=24
Selection.AutoFilter
p1 = Application.WorksheetFunction.Match("Assy&Test", Worksheets("Rev (rep)").Range("A:A"), 0) + 1
q = Application.WorksheetFunction.Match("Del", Worksheets("Rev (rep)").Range("A:A"), 0) + 1
p2 = q - 3
Rows(p1 - 1).Select
Selection.AutoFilter
Selection.AutoFilter Field:=24, Criteria1:="<>"
Rows(p1 & ":" & p2).Select
MyCnt = Evaluate(Application.Subtotal(103, Range("X" & p1 & ":X" & p2)))
If MyCnt < 1 Then
  'do nothing
Else
  Selection.SpecialCells(xlCellTypeVisible).Select
  Selection.Delete Shift:=xlUp
End If
Selection.AutoFilter Field:=24
Selection.AutoFilter
q = Application.WorksheetFunction.Match("Del", Worksheets("Rev (rep)").Range("A:A"), 0) + 1
Rows(q - 1).Select
Selection.AutoFilter
Selection.AutoFilter Field:=24, Criteria1:="<>"
LR = Cells(Rows.Count, 2).End(xlUp).Row
Rows(q & ":" & LR).Select
MyCnt = Evaluate(Application.Subtotal(103, Range("X" & q & ":X" & LR)))
If MyCnt < 1 Then
  'do nothing
Else
  Selection.SpecialCells(xlCellTypeVisible).Select
  Selection.Delete Shift:=xlUp
End If
Selection.AutoFilter Field:=24
Selection.AutoFilter
Application.ScreenUpdating = True
End Sub


Then run the RevRep macro, click on the command button on worksheet Rev (rep).
 
Upvote 0
Hello Hiker!

Sorry it took a wee while to get back to you.

You are a star. This is an application I would not have thought of. more importantly it works perfectly.

Many thanks indeed.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Locked
  • Solved
select a cell in a filtered row
Replies
3
Views
96
Peter_SSs
Peter_SSs
I
  • Question Question
Filter column on text value if that value exists using VBA
Replies
7
Views
385
Peter_SSs
Peter_SSs
O
  • Question Question
Cannot trap error when using Excel's built-in stock price functionality in a macro. It used to work well!
Replies
1
Views
249
OaklandJim
O
Y
  • Solved
AutoFilter with Dates - Date range, yes; single date, no
Replies
7
Views
373
Alex Blakenburg
A
SanjayGMusafir
  • Solved
VBA to add new record - Declaring variable
2
Replies
14
Views
769
Micron
M

Forum statistics

Threads
1,224,521
Messages
6,179,285
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top