Excel 2007 : VB code to hide rows that are empty or contain "-"

It helps to know how the values in that column get there, by manual insertion or by formula. But a generic way to examine all of column A on a given sheet would be:

Option Explicit

Sub HideRows()
Dim RNG     As Range
Dim HideRNG As Range
Dim Cel     As Range

Set RNG = Range("A1:A" & Cells.SpecialCells(xlLastCell).Row)

For Each Cel In RNG
    If Cel.Value = "" Or Cel.Value = "-" Then
        If HideRNG Is Nothing Then
            Set HideRNG = Cel
        Else
            Set HideRNG = Union(HideRNG, Cel)
        End If
    End If
Next Cel

If Not HideRNG Is Nothing Then
    HideRNG.EntireRow.Hidden = True
    Set HideRNG = Nothing
End If

Set RNG = Nothing
End Sub

Is there a reason for not just using an autofilter?

Thanks for the feed back folks. I'm new with VB. I'm using the following code (as it's the only one i seem to understand) but I get an error when I try to compile it. By the way the object is sheet1.

Run-Time error '424, Object required

Function hidrows()
Template.ActiveSheet.Protection.Enabled = False
Template.ActiveSheet.Calculate

For intRow = 1 To Template.ActiveSheet.UsedRange.Rows.Count
    If Template.Cells(intRow, 1).Value = "X" Or Template.Cells(intRow, 1).Value = "x" Then
        For intCol = 1 To Template.ActiveSheet.UsedRange.Columns.Count
            If Template.Rows(intRow).EntireRow.Hidden = False Then
                Template.Rows(intRow).EntireRow.Hidden = True
            End If
        Next
    End If
Next

Template.ActiveSheet.Protection.Enabled = True

End Function

What is Template supposed to be?

Also, you must use code tags when posting code on the forum. I have added them for you on this occasion, but you must use them yourself in future, please.

okay i removed the Template and not got a different error. Error 438, Object doesn't support this property or method. added quoted text around my code, hope that is what you meant by code tags.

Function hidrows()
ActiveSheet.Protection.Enabled = False
ActiveSheet.Calculate

For intRow = 1 To ActiveSheet.UsedRange.Rows.Count
    If Cells(intRow, 1).Value = "X" Or Cells(intRow, 1).Value = "x" Then
        For intCol = 1 To ActiveSheet.UsedRange.Columns.Count
            If Sheet1.Rows(intRow).EntireRow.Hidden = False Then
                Sheet1.Rows(intRow).EntireRow.Hidden = True
            End If
        Next
    End If
Next

ActiveSheet.Protection.Enabled = True

End Function

Highlight your code and click the # at the top of your post window. For more information about these and other tags, see here

You can also just type them in:
[code] your code goes here [/code]

Your unprotect and protect part is incorrect - it should be:

ActiveSheet.UnProtect

and:

ActiveSheet.Protect

Why are you looping through the columns when you hide the entirerow?

Tried your suggestion but i still get the same error.

I only want to loop through the first column as there will be a formula that will return x if there is not data. I see what your mean. how do i modify my code to only loop through column 1?

thx

What did you actually try?