How to hide all Rows that are not highlighted?

Question

First of all, I am a beginner in programming VBA. That said, here is the deal: I have a code that can find and highlight all occurrences of multiple keywords (if you want to see my code, it is here). At the end of this code there is the following If statement:

If Not rng Is Nothing Then
    rng.EntireRow.Interior.ColorIndex = 3
End If

And now I would like to hide the lines that are not highlighted. After a lot of reasearch (References: 1, 2, 3, 4, 5.) on how to hide Rows I had this idea:

If Not rng Is Nothing Then
    rng.EntireRow.Interior.ColorIndex=3
Else
    rng.EntireRow.Hidden = True
End if

But it doesn't work at all. Frustrated, because I thought that it was really a good idea, I created a new Workbook and tried to create a simple Macro to achieve my objective and afterwards I would add it in the searching and highlight Macro described above. Right now it looks like this:

Sub Test()
Dim i As Integer
Dim ws As Worksheet

    Set ws = Tabelle1

    For i = 1 To 10
        If ws.Rows(i).Interior.ColorIndex = 3 Then
            MsgBox "Super"
        Else
            ws.Rows(i).Hidden = True
        End If
    Next i

End Sub

This Macro has a problem, when I run it once it finds a Row that is not highlighted it will hide all the other Rows independent if they are highlighted or not. Can some one explain me what is wrong with my code that makes the Macro run this away. If someone could also point out how can I make it run with the Else statement from my previous idea it would be great!

score 2 · Accepted Answer · answered Jun 30 '15 at 13:00

2

Your first bit of code will not work. Period. You're testing to see if the variable rng is set to something, if so you can use it (and you do). If it's not set to something (i.e. its value is Nothing), then you can't use it for any purpose because rng doesn't point to anything. You can't hide nothingness.

Your second block of code works just fine for me. I modified it slightly to work in a new, blank workbook, as follows:

Sub Test()
Dim i As Integer
Dim ws As Worksheet

    Set ws = Sheets("Sheet1")

    For i = 1 To 10 Step 2
      ws.Rows(i).Interior.ColorIndex = 3
    Next

    For i = 1 To 10
        If ws.Rows(i).Interior.ColorIndex = 3 Then
            MsgBox "Super"
        Else
            ws.Rows(i).Hidden = True
        End If
    Next i

End Sub

Worked like a charm for me.

answered Jun 30 '15 at 13:00

FreeMan

5,660
1
27
53

That is really strange. Because I posted your code, and my code as well in a new Workbook and it worked as it was supposed to. But in my other Workbook (that like previously said it was a complety new one as well) it still hide all the Rows after the first not highlighted Row. Any idea why this happened? – Hugo Cavalini Agudelo Jun 30 '15 at 13:16
1

You would probably have to step through your code in debug to see what the `Interior.ColorIndex` is for each row. You may be seeing `red`, but the actual `ColorIndex` may not be `3`. – FreeMan Jun 30 '15 at 13:30
I just ran it on debug mode, it was 3 and the code work normal (It didn't hide all the lines after the first non-highlighted one). It was really strange (bug?) and took me unfortunately a lot of time. I will accept your answer because you also helped me explaining why my first try didn't work. Many Thanks, Answer accepted. – Hugo Cavalini Agudelo Jun 30 '15 at 13:45
I appreciate the check mark, @HugoCavaliniAgudelo. It would be good to post your final resolution to help others out. Either post an answer, or edit your original post to **ADD** in the resolution (don't change the existing problem description). – FreeMan Jun 30 '15 at 13:59

score 1 · Answer 2 · answered Jun 30 '15 at 12:56

The problem lies in that the ColorIndex property of the Rows object returns Null. Instead, test for a ColorIndex against a single cell. In your case, the rng variable should refer to a single cell (if I read your code correctly), so test the ColorIndex of rng. If colored, then respond however you please.

Let me know if that helps.

score 0 · Answer 3 · answered Mar 27 '21 at 18:29

I created a macro to hide other columns and rows except highlighted cells. The macro is run by pressing the F9 key. I hope it will be useful to you.

Sub Hide_Columns()
    Dim i, a As Long, s, t As Integer
    If TypeName(Selection) <> "Range" Then Exit Sub
    If Rows(Rows.Count).EntireRow.Hidden Or Columns(Columns.Count).EntireColumn.Hidden Then
        Cells.EntireColumn.Hidden = False
        Cells.EntireRow.Hidden = False
        Exit Sub
    End If
    i = Selection.Rows(1).Row
    a = i + Selection.Rows.Count - 1
    s = Selection.Columns(1).Column
    t = s + Selection.Columns.Count - 1
    Application.ScreenUpdating = False
    On Error Resume Next
    Range(Cells(1, 1), Cells(i - 1, 1)).EntireRow.Hidden = True
    Range(Cells(a + 1, 1), Cells(Rows.Count, 1)).EntireRow.Hidden = True
    ...

Source :Excel hide all columns except selected

How to hide all Rows that are not highlighted?

3 Answers3