How to hide rows based on cell value in VBA (excel) 1

[schredder]

Hi all
I have a sheet in which in coloumn Q i have either an "X" in it or the cell is empty. I would like to code that when i execute it all the rows which have an "X" in the according cell are hidden. I coded it like this, it's working but it is very clumsy, bascially I would end up having a code line for every row in the sheet :-( Can somebody help please. Thanks so much.

Sub rows_theta_grafik()
Cells(1, 17).Activate
If Cells(1, 17) = "X" Then
Selection.EntireRow.Hidden = True
End If
Cells(2, 17).Activate
If Cells(2, 17) = "X" Then
Selection.EntireRow.Hidden = True
End If
Cells(3, 17).Activate
If Cells(3, 17) = "X" Then
Selection.EntireRow.Hidden = True
End If
Cells(4, 17).Activate
If Cells(4, 17) = "X" Then
Selection.EntireRow.Hidden = True
End If
Cells(5, 17).Activate
If Cells(5, 17) = "X" Then
Selection.EntireRow.Hidden = True
End If
Cells(6, 17).Activate
If Cells(6, 17) = "X" Then
Selection.EntireRow.Hidden = True
End If

Range("F9").Select
End Sub

 

[combo]

Dim c As Range
For Each c In Range("Q1:Q6")
    If c = "X" Then c.EntireRow.Hidden = True
Next c

combo

 

[schredder]

Thanks combo. Worked. Still takes kinda long for 400 rows, maybe there is a simple code for selecting all rows with X in one shot and hide this selection ??

 

[xlbo]

....hmmmmm.....maybe the autofilter.......

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

"Still takes kinda long for 400 rows..."

As Geoff suggested, Data > AutoFilter

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[AnotherHiggins]

loops are slow - autofilters are fast.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[combo]

Or by code, find all cells first, and next hide rows in one go:

Dim c As Range, u As Range
For Each c In Range("Q1:Q1000")
    If c = "X" Then
        If u Is Nothing Then
            Set u = c
        Else
            Set u = Union(u, c)
        End If
    End If
Next c
u.EntireRow.Hidden = True

combo

 

[schredder]

combo, that's great. thanks alot.