VBA to hide rows based on row counta() value

Question

I have a workbook that will contain several worksheets.  Each sheet will have a range of rows that needs to be hidden based on the count of non blank cells in that row.

Each row in the range for each sheet needs to be hidden if the count of all the cells in that row that are not blank equals 1.   So in other words there will always be text in Column A, but if the rest of the row is blank then that row should be hidden.

I also need a macro to unhide the rows.

Sheet1

For each row in the range of rows 10 - 21 

Sheet2

For each row in the range of rows 9 - 24

Sheet3

For each row in the range of rows 9 - 16 and 18 - 23

I have very limited VBA experience.  (I'm trying to learn!)  Any help is greatly appreciated.

Jordan

Answer 1

Here you go:

Sub HideRows()

    Dim r As Long

    With Worksheets("Sheet1")

        For r = 10 To 21

            If Application.CountA(.Rows(r)) = 1 Then

                .Cells(r, 1).EntireRow.Hidden = True

            End If

        Next r

    End With

    With Worksheets("Sheet2")

        For r = 9 To 24

            If Application.CountA(.Rows(r)) = 1 Then

                .Cells(r, 1).EntireRow.Hidden = True

            End If

        Next r

    End With

    With Worksheets("Sheet3")

        For r = 9 To 23

            If r <> 17 Then

                If Application.CountA(.Rows(r)) = 1 Then

                    .Cells(r, 1).EntireRow.Hidden = True

                End If

            End If

        Next r

    End With

End Sub

Sub Unhiderows()

    Worksheets("Sheet1").Range("A10:A21").EntireRow.Hidden = False

    Worksheets("Sheet2").Range("A9:A24").EntireRow.Hidden = False

    Worksheets("Sheet3").Range("A9:A23").EntireRow.Hidden = False

End Sub

Answer 2

You'd use

If r <> 17 And r <> 19 Then

Answer 3

HansV - 

Thank you so much for your quick reply.  It works perfect!!  

I read through the If statements (best I could) and see that you eliminated row 17 on Sheet2, which is perfect.

If I wanted to eliminate an additional row or two from the selection is there an OR that I could add?

For example if I wanted row 17 and row 19 to be ignored?

Thanks again for your help with this.  You guys are amazing.

Jordan.