Hide and then Delete Hidden Rows based on a Cell Value

[WSBirch]

Hi all,
I've been working on this all day trying various solutions I've found online here and for whatever reasons, probably user error, I'm just not able to get them to run correctly. Mostly no errors, just not performing the VBA.

I have at least 500 rows of data. Usually anywhere from 500 - 3000 rows at any given time.

In column "AI", I have either a value of " 0 " or " 1 ". I would like all rows that have a " 0 " in column "AI" to be hidden. Once all rows are hidden, then delete all hidden rows.

If this is of any impact, it's doing this in a table.
The table is refreshed, then rows hidden, and then hidden rows deleted. That's the entire macro I'm trying to create if possible.

All help would be greatly appreciated, thank you!

 

[DanteAmor]

Try this
(If the rows are going to be deleted it makes no sense to hide them.)

Sub DeleteRows_4()
  With ActiveSheet
      .Columns("AI").Replace what:=0, replacement:="#N/A", Lookat:=xlWhole
      On Error Resume Next
      .Columns("AI").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
  End With
End Sub

 

[WSBirch]

Actually, yeah, no reason to hide the rows if I can just delete them. I definitely like that.

Unfortunately, none of the rows are deleted. Nothing actually happens other than the refresh.
The values in the column AI are formulas. I know Excel can get hinky with that kind of thing. Here's the formula in one of those cells:

=VALUE(IF(OR([@SchShipDate]<TODAY(),[@SchShipDate]>TODAY()+14),0,1))

Here's the full code that I'm using right now. I don't receive any kind of errors. It completes the whole macro, but nothing happens other than just refreshing the query.

Sub Refresh()

    Sheets("Sheet1").Select
    Range("Current_data_without_product_details__2[[#Headers],[ORDNO]]").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    
      With ActiveSheet
      .Columns("AI").Replace what:=0, replacement:="#N/A", Lookat:=xlWhole
      On Error Resume Next
      .Columns("AI").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
      End With
 
End Sub

 

[DanteAmor]

Assuming your header is in row 1, try:

Sub DeleteRows_5()
  Dim lr
  lr = Range("AI" & Rows.Count).End(xlUp).Row
    With Range("A1:AI" & lr)
        .AutoFilter Field:=35, Criteria1:="=0"
        ActiveSheet.AutoFilter.Range.Offset(1, 0).EntireRow.Delete
    End With
    Range("A1").AutoFilter
End Sub

 

[WSBirch]

Yes, the header was in row 1. It worked! Thank you so much!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.