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"
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).
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.
|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|