Hiding Entire Rows Using VBA

[tf_2119]

I'm working on a project that needs to hide entire rows if one of the cells in each row does not contain one of two text options. I have three tabs, titled 'Bidding', 'Open Jobs', and 'Job Results'. Bidding is used to enter new data, Open Jobs reflects all the same information from the Bidding tab but is not for editing, and the Job Results tab is meant to only reflect completed bids.

I need to remove bid results marked "Unknown/TBD" and/or "0" (I'm using index/match from the Open Jobs tab), and leave only rows that reflect "Won" or "Lost".

I'm very new to VBA, so I'm blindly entering formulas in hopes of stumbling across the correct syntax. Would anyone know some useful codes that would hide rows based on the text in a particular cell?

Thanks!

 

[Joe4]

Welcome to the Board!

I don't think you need VBA to do this. I think you should be able to use simple Data Filters to show the rows you want, and hide the ones you don't.

 

[DanteAmor]

I need to remove bid results marked "Unknown/TBD" and/or "0" (I'm using index/match from the Open Jobs tab), and leave only rows that reflect "Won" or "Lost".

If the goal is to hide from the 'Bidding' sheet "Unknown / TBD" and / or "0" and the visible records put on the 'Open Jobs' sheet, try the following macro. Change "D" in this instruction: col = "D" by the letter of the column with the content to filter.

Sub Hide_entire_rows()
    Dim ws1 As Worksheet, ws2 As Worksheet, col As String
    
    Application.ScreenUpdating = False
    
    Set ws1 = Sheets("Bidding")
    Set ws2 = Sheets("Open Jobs")
    col = "D"
    
    ws2.Cells.ClearContents
    If ws1.AutoFilterMode Then ws1.AutoFilterMode = False
    ws1.Range("A1").CurrentRegion.AutoFilter Field:=Columns(col).Column, _
        Criteria1:="<>Unknown/TBD", Operator:=xlAnd, Criteria2:="<>0"
    ws1.Range("A1").CurrentRegion.Copy
    ws2.Range("A1").PasteSpecial xlValues
    If ws1.AutoFilterMode Then ws1.AutoFilterMode = False
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
End Sub

Try and tell me

 

[tf_2119]

Dante - that seemed to partially work. It hid the "0" and "UNKNOWN/TBD" in the particular column's cells, but the rows remained. Is there a way to have the entire row hidden if it doesn't contain "WON" or "LOST" in column L?

 

[DanteAmor]

Remove the second line

[COLOR=#333333]If ws1.AutoFilterMode Then ws1.AutoFilterMode = False[/COLOR]

Sub Hide_entire_rows()
    Dim ws1 As Worksheet, ws2 As Worksheet, col As String
    
    Application.ScreenUpdating = False
    
    Set ws1 = Sheets("Bidding")
    Set ws2 = Sheets("Open Jobs")
    col = "D"
    
    ws2.Cells.ClearContents
    If ws1.AutoFilterMode Then ws1.AutoFilterMode = False
    ws1.Range("A1").CurrentRegion.AutoFilter Field:=Columns(col).Column, _
        Criteria1:="<>Unknown/TBD", Operator:=xlAnd, Criteria2:="<>0"
    ws1.Range("A1").CurrentRegion.Copy
    ws2.Range("A1").PasteSpecial xlValues
        
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
End Sub