How to set a skip a step when filter result is 0?

[Martin1991]

Hello,
I m trying to figure out, how to set my formula to skip a similar step after filtering out any values with 0 results. I m searching second day and can´t find any tip for this specific case. I´m beginner with VBA, so i hope it´ll be easy question for you.

To clarify, i got small simple macro, which should fill special cells with some "memo" according the criterial values.
1. Macro filters out, what i need.
2. Fills special cells with "memo".
3. Reset my filters.

Problem is step 2. When filter can´t find defined criteria (because i work daily with another documents, and sometimes there are those criterias, sometimes not), it fills up not only special cells, but every single cell (not only visible) in current column except the first raw, which is header.

Sub HQstatuses()

    Sheets("Data").Select
    ActiveSheet.UsedRange.AutoFilter Field:=88, Criteria1:=Array( _
        "HQ can´t support*", "HQ pending - shortage*"), Operator:=xlFilterValues
    
    ActiveSheet.UsedRange.AutoFilter Field:=65, Criteria1:=Array( _
        "Y"), Operator:=xlFilterValues
        
    x = ActiveWorkbook.Sheets("Data").Range("CA1").CurrentRegion.Rows.Count


    vzoreccheck = "oow/cid-HQ pending - shortage, please choose a sub with different color"
    
    Range(Cells.SpecialCells(xlCellTypeVisible)(2, 79), Cells.SpecialCells(xlCellTypeVisible)(x, 79)).Value = vzoreccheck
    ActiveSheet.Calculate
    ActiveSheet.ShowAllData
    
End Sub

What kind of changes would you recommend to me? I think, its about some IF condition, tryed to set it by IF with another formula "y": y = SpecialCells(xlCellTypeVisible).Count ---> If y > 1 Then calculate, but it doesn´t work.

I´m new to the Forum, read all board rules, so i hope its all understandable.

 

[Fluff]

Welcome to the board.
Give this a go

Sub HQstatuses()

    Sheets("Data").Select
    x = Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ActiveSheet.UsedRange.AutoFilter Field:=39, Criteria1:=Array( _
        "HQ can´t support*", "HQ pending - shortage*"), Operator:=xlFilterValues
    
    ActiveSheet.UsedRange.AutoFilter Field:=35, Criteria1:=Array( _
        "Y"), Operator:=xlFilterValues
        


    vzoreccheck = "oow/cid-HQ pending - shortage, please choose a sub with different color"
    
    On Error Resume Next
    Range("CA2:CA" & x).SpecialCells(xlCellTypeVisible).Value = vzoreccheck
    On Error GoTo 0
    ActiveSheet.Calculate
    ActiveSheet.ShowAllData
    
End Sub

 

[Martin1991]

I tryed it and seems it works properly. I will test it for a few days and will post, if it doesn´t make any errors.
But still can´t get the point, why just this formula. On Error means "if finds no data"?
Anyway thanks a lot. Right now it works well.

 

[Fluff]

On Error Resume Next

Means that if there are no visible cells in the range the code will simply ignore any errors & skip to the next line of code. But you should always follow this as soon as possible with

On Error GoTo 0

Which then re-sets the error handler back to it's default setting

 

[Martin1991]

Thank you, it works well and i trust, it will help me with another macros.

 

[Fluff]

Glad to help & thanks for the feedback

 

[Martin1991]

Glad to help & thanks for the feedback

Can i ask u one more think even its kind of out of topic? But its still connected with this makro.

Dim x    x = ActiveWorkbook.Sheets("Sheet1").Range("BZ1").CurrentRegion.Rows.Count
    
    For Each c In Worksheets("Sheet1").Range("BZ1:BZ5000")
     If c.Value > 88 Then
        Range(Cells.SpecialCells(xlCellTypeVisible)(2, 78), Cells.SpecialCells(xlCellTypeVisible)(x, 78)).EntireRow.Select
        Selection.Style = "Bad"
     End If

I found only solutions on delete rows, which are in some condition, but not for this filling. I understand the problem is in my range, but not sure.
If i want this code to fill only rows with value (in that specified column BZ) which is higher than 88, then should i change condition or range settings? For now its filling every filtered raw and not rows with c.Value.

 

[Martin1991]

I´m sorry, i forgot to copy in code "Next c". It is actually there, only my mistake of copying.

 

[Fluff]

Try

Dim x, c
x = ActiveWorkbook.Sheets("ma").Range("Z1").CurrentRegion.Rows.Count
    
    For Each c In Worksheets("ma").Range("Z2:Z5000")
     If c.Value > 88 Then
        c.EntireRow.Style = "Bad"
     End If
    Next

 

[Martin1991]

Well, its doing everything right, but there is small problem, i forgot to mention. Before this part of macro, there is step with filter: (filter Y, from Y/N options), and it works for every non-filtered cells aswell. I hope I´m writting it understandable. Thats, why i tryed to get in that SpecialCells(xlCellTypeVisible).