OzGrid

How to use Excel VBA code to hide based on criteria

< Back to Search results

 Category: [Excel]  Demo Available 

How to use Excel VBA code to hide based on criteria

 

Requirement:

 

The user has a code to sort a worksheet however the user wants to include a special provision where if the text in the Range of column "C" equals "TOTAL" do not hide the empty row under it.

For example the user wants the worksheet to have a row separating my tables after the, text TOTAL.

Right now it hides every row with a empty space in the range but i want to not hide the row if the cell above that CURRENT cell reads "TOTAL"

 

Solution:

 

Code:
Sub You()
    Dim xRg As Range
    
    Application.ScreenUpdating = False

    For Each xRg In Range("C3:C420")
        If xRg.Value = "" Then
            If xRg.Offset(-1, 0).Value <> "TOTAL" Then
                xRg.EntireRow.Hidden = True
            End If
        End If
    Next xRg
    
    Application.ScreenUpdating = True

End Sub

Note that deleting rows can be tricky - you might want to look at looping backwards (ie from the bottom to the top).

 

OR

 

Code:
Sub HideSelectively()
Dim xRg As Range
Application.ScreenUpdating = False
    For Each xRg In Range("C3:C420")
      If xRg.Value = "" And xRg.Offset(1, 0) <> "TOTAL" Then
        xRg.EntireRow.Hidden = True
      Else
        xRg.EntireRow.Hidden = False
      End If
    Next xRg
Application.ScreenUpdating = True
End Sub

 

Obtained from the OzGrid Help Forum.

Solution provided by Excel_Phoenix and Carim.

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

How to create VBA for index and match based on sheet criteria
How to use VBA code to clear cells based on specific criteria
How to use a macro to select value criteria from a table rather than manually inputting
How to move row contents to another sheet based on criteria
How to use a code to display the current date based on certain criteria

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


Gallery



stars (0 Reviews)