Auto hide unused rows, automatic update

Hi,

I have a VBA-code that I want to modify. The code hides all unused rows and it lets you select starting column and row. I have assigned the macro to a clickable box, so whenever I click the box the unused rows get hidden. However, I would like to make it so that the unused rows automatically get hidden without the need to button pressing, and get unhidden when a row no longer is empty. I would also like to add some rows to be ignored and thus always be shown.

Sub HideUnusedRows()
    Dim HiddenRow&, RowRange As Range, RowRangeValue&
    Const FirstRow As Long = 3
    Const LastRow As Long = 13
    Const FirstCol As String = "C"
    Const lastcol As String = "Z"
    ActiveWindow.DisplayZeros = False
    Application.ScreenUpdating = False
    For HiddenRow = FirstRow To LastRow
    Set RowRange = Range(FirstCol & HiddenRow & _
    ":" & lastcol & HiddenRow)
    RowRangeValue = Application.Sum(RowRange.Value)
    If RowRangeValue <> 0 Then
    Rows(HiddenRow).EntireRow.Hidden = False
    Else
    Rows(HiddenRow).EntireRow.Hidden = True
    End If
    Next HiddenRow
    Application.ScreenUpdating = True
End Sub

Originally Posted by TMShucks

How will "get unhidden when a row no longer is empty"? If you can't see the row, how are you going to put anything in it? Or am I missing something?

Regards

I have a drop down list with different brands on B1. On A1:A13 I have measurements, like shoe size on A2 and color on A3. If I pick pants in B1, A2 will be empty. And if I repick shoes in B1, A2 will not be empty. I want the macro to kick in everytime I pick something on a drop down list and hide the unused rows.

Hi Ranew,

As a workaround, what I would recommend you is to:
- Create a column called (for example) SUM_ALL where it would contain the sum of the values from columns A to Z
- Then use the Worksheet_Change event in the sheet you want to do that. In that event you would re-apply a filter where SUM_ALL <> 0

See example below (considering your table is from A1 to Z:1000, for example. And that the SUM_ALL column is AA, but you can adjust that using your Const). Please notice the code below will have to be inside the sheet with that filter:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Range("$A$1:$Z$100").AutoFilter Field:=27, Criteria1:="<>0", Operator:=xlFilterValues
'Field:=27 --> Column AA
End Sub

Good luck

Thank you for your suggestion inesi. Will your method automatically hide/unhide whenever a row is empty? I also have words, numbers, strings of text and hyperlinks on the rows, will a sum all still work?

Hi Ranew, thank you for the clarification. In this case you are right, the sum won't work. What you can do instead is to have it replaced by functions like COUNTA (to count non empty cells) or CONCATENATE. The main idea behind the SUM_ALL column is to have a column where it can set if the columns A to Z are empty so the filter and macro can work. Try that and let us know.