Excel VBA Code to Hide rows based on Criteria

Ymussa · Apr 12th 2019

Hey i have this code that i use to sort my worksheet however i want 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 i want 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"

this is the code :

Code

Sub You ()


[I] Dim xRg As Range Application.ScreenUpdating = False
For Each xRg In Range("C3:C420")
If xRg.Value = "" Then
xRg.EntireRow.Hidden = True
Else
xRg.EntireRow.Hidden = False
End If
Next xRg
 Application.ScreenUpdating = True


End Sub [/I]

Display More

Excel_Phoenix · Apr 12th 2019

Hi,

How about something like this:

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

Display More

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

**Carim** · Apr 12th 2019

Hi,

You can test the following :

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

Display More

Hope this will help

Edit : Oops ... too Late ... :smile:

Ymussa · Apr 12th 2019

Thanks [USER="31712"]Carim[/USER] didnt get a chance to use yours because [USER="333350"]Excel_Phoenix[/USER] Code worked great!

Thanks for help !

Ymussa · Apr 12th 2019

[USER="31712"]Carim[/USER] just checked your code works when you change the offset to (-1,0) but good job dude! great work

**Carim** · Apr 12th 2019

Quite honestly was a bit confused ... since in your initial message ...

the first sentence says :

Quote

do not hide the empty row under it.

the second sentence says :

Quote

not hide the row if the cell above that CURRENT

Now you do have both ....:sing:

Excel VBA Code to Hide rows based on Criteria

Participate now!

Share