Macro to Hide Blank Rows

[mike4]

I know that this has been a common question on these boards, but after searching through previous posts - and my very rudimentary understanding of VBA/Macros - I've not found anything that works for me.

Basically, here is what I have set up:

On my "Summary" tab, I have a chart built that takes up Cell A2 - I27, with row 27 being the totals of rows 2-26. There is a formula in every cell of the chart that determines whether or not to display data based on criteria established on my "Worksheet" tab. An example of the formula is =IF(Worksheet!C3="","",Worksheet!C3). Once data is entered on the "Worksheet" tab, the corresponding information appears on the "Summary" tab.

In most cases not every row will be filled up, meaning there will be blank rows between the last line of data and the total line. I would like to insert a command button entitled "Hide Empty Rows" that would do just that. This will make it much easier on our sales guys to quickly consolidate this information into a clean chart ready to email. I am using Excel 2010 if that matters for this.

Any help would be appreciated. Again, I realize that this is a common topic here, but I have tried many different things and none have worked for me so far. I thin it may have to do with the fact that there are formulas in the cells, so they are not truly blank.

- Mike

 

[JoeMo]

Since the last row of your summary sheet is a 'totals' row, I'm making the assumption that all the formula cells above this row return either a number or "". If that's the case, try this:

Sub HideEmptyRows()
Dim hRws As Range, Rw As Range, R As Range
Set R = Range("A2:I26")
For Each Rw In R.Rows
    If WorksheetFunction.CountA(Rw) > WorksheetFunction.Count(Rw) Then
        If hRws Is Nothing Then
            Set hRws = Rw
        Else
            Set hRws = Union(Rw, hRws)
        End If
    End If
Next Rw
If Not hRws Is Nothing Then
    hRws.EntireRow.Hidden = True
End If
End Sub

You can add a command button to the summary sheet and assign this macro to it.

 

[mike4]

Thanks for the response JoeMo. When I run that code though, it is hiding every row, even those rows with data. I only need it to hide the blank rows.

Any suggestions? Or did I do something incorrectly on my end?

 

[mike4]

Looking at it some more, Any suggestions on how to hide rows where column A is blank? That is the column that pulls our part number from the "Worksheet" tab. If that cell is blank, than columns B-I don't populate...

Would that be an easier fix?

Thanks again.

- Mike

 

[djnrempel]

Kind of an old thread, but it was the one I found and now I found an answer that worked for me.

I activated Filters on my table using the Data ribbon, and then filtered the table to exclude blanks.

I was able to record this using the Record Macro function and it created macros as follows. Now I can use Ctrl-U and Ctrl-H to hide and unhide the blank rows as needed.

Sub HideBlanks ()
'
' HideBlanks Macro
'
' Keyboard Shortcut: Ctrl+h
'
ActiveSheet.Range("$A$2:$I$142").AutoFilter Field:=4, Criteria1:="<>"
End Sub




Sub Unhide()
'
' Unhide Macro
'
' Keyboard Shortcut: Ctrl+u
'
ActiveSheet.Range("$A$2:$I$142").AutoFilter Field:=4
End Sub