Error handling query

[mkvarious]

hello,

so there's a macro that our client started to develop for us (I realize that sounds a little bit crazy, but forget it) and now I am developing it as they do not want to do it anymore.

what macro does is more or less sorting rows in descending order of manufacturing recommendation and then applying some formatting to selected rows.

now, I do not really like the code as it loops through first row of data to last row, checks each row individually and then applies formatting. so I have changed the macro to use autofilter to get lines meeting the criteria visible and available for formatting. so now it works quicker.

there are 33 rules for formatting and I am using very similar block of the code presented below. I am filtering the sheet and visible rows get formatted on selected column/columns.

my query is connected to error handling as I have observed the following:
if my range of xlCellsVisible (range: formatuj) cannot be set for the second time within the module, then 1004 Run-time 'No cells were found' error pops up.

now I can deal with that as I insert row("1:1") and set 'formatuj' range from the 1 row to last row of data. so even if not all filter criteria are found on data rows then each time there will be at least one row visible within the 'formatuj' range (row 1) and then there is no 1004 Run-time error at all. then after 33 filters are applied (or not), row 1 gets deleted and the job is done.

But I would like to ask advanced users how this can be possibly handled the other way, if I wanted not to insert row 1 and set 'formatuj' range from the 3rd row where data starts, instead?
NOTE: I have even tried to number each 'formatuj' range subsequently, like 'formatuj1', 'formatuj2', 'formatuj3' to 33 but it gave me nothing.
ALSO, I have tried to handle errors with 'on error goto 0' but it gave me nothing, again.

what do I wrong, if anything - please enlighten me.
I remember I had the same problem two years ago when I wrote my very first macro and then I ended up with moving the part of code to different module and there was/is no errors, till today.
with the macro in subject I'd have to build 33 modules to get rid of runtime errors, had I not found the solution with inserting row 1 and changing the 'formatuj' range???

thanks in advance
mkvarious

 With ActiveSheet
    With .Range(Cells(1, "A"), Cells(ostWIERSZ + 1, ostKOLUMNA))
        .AutoFilter Field:=CMPGNsrCOLUMN, Criteria1:=">0"
        .AutoFilter Field:=clusterCOLUMN, Criteria1:=Array("DSK-00", "DVD-99", "DVD-77", "NR-DVD", "DVD-00", "DVD-99P", "DVD-99S"), Operator:=xlFilterValues
    End With
    On Error GoTo brak_wyników_rule
    Set formatuj = Union(Range(Cells(3,  skuCOLUMN), Cells(ostWIERSZ + (1), skuCOLUMN)).SpecialCells(xlCellTypeVisible), _
    Range(Cells(3, titleCOLUMN), Cells(ostWIERSZ + (1), titleCOLUMN)).SpecialCells(xlCellTypeVisible))
    On Error GoTo 0
    With formatuj
        .Font.Italic = True
    End With
brak_wyników_rule:
    On Error GoTo 0
    .ShowAllData
End With

 

[Jonmo1]

See if this discussion helps.

http://www.mrexcel.com/forum/excel-questions/684389-error-handling-not-working-expected.html

 

[Peter_SSs]

mkvarious

It's a bit hard to work with your actual code since it contains a lot of variables we don't know about.
However, see if this helps as a method of dealing with your problem.
Set up a sheet like below and run (or step through) the code.
Then change "Fred" in the code to "John" and run it again.

Excel Workbook
	A	B
1
2
3	Name	Grade
4	John	A
5	Bill	B
6	John	A
7	Tom	B
8	Mary	B
9	Ken	A
10	Ken	B
11	Mary	B
12
Error handler

Sub Handle_Visible_Rows()
  Dim VisRng As Range
  
  With Range("A3", Range("B" & Rows.Count).End(xlUp))
    .AutoFilter Field:=1, Criteria1:="Fred"
    Set VisRng = .SpecialCells(xlVisible)
  End With
  If VisRng.Rows.Count > 1 Then
    MsgBox "Visible rows after AutoFilter"
  Else
    MsgBox "No visible after AutoFilter"
  End If
  ActiveSheet.ShowAllData
End Sub