Macro to hide a specific row in column C with the following macro which I want to use to unhide as a toggle

[SeanOZ]

Hi all,

I have a macro which hides all rows in a column within my specific spreadsheet as per the ranges used.

I would like to set my macro up to toggle between hiding and then unhiding so need something to reverse the process.

The following is the code I have used:
Sub HideUnusedRows()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range


With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With


With ActiveSheet
.DisplayPageBreaks = False
StartRow = 6
EndRow = 127
For Lrow = StartRow To EndRow Step 1
If IsError(.Cells(Lrow, "C").Value) Then
'Do nothing, This avoid a error if there is a error in the cell
ElseIf .Cells(Lrow, "C").Value = "0" Then
If rng Is Nothing Then
Set rng = .Cells(Lrow, "C")
Else
Set rng = Application.Union(rng, .Cells(Lrow, "C"))
End If
End If
Next
End With
'hide all rows in one time
If Not rng Is Nothing Then rng.EntireRow.Hidden = True


With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


I appreciate any help.

Kind regards,

Sean

 

[MARK858]

Wouldn't it be more efficient to use Autofilter either manually or via VBA?

 

[SeanOZ]

Hi,

I am still a novice with regards to writing macros, so if you could provide me with a code to use the Autofilter that would be fantastic.

Or alternatively just happy for a reverse of my current code.

Kind regards,

Sean

 

[MARK858]

ActiveSheet.Range("$C$5:$C$127").AutoFilter Field:=1, Criteria1:="<>0"

Click the Clear or Autofilter buttons to show the zero's, then click Undo if you want the filter toggled back.

 

[SeanOZ]

Hi,

Thank you for your follow up.

So do I just replace my current code with this one liner.

I have tried and does not seem to work.

This is what I have:
Sub HideUnhideRow()
ActiveSheet.Range("$C$5:$C$127").AutoFilter Field:=1, Criteria1:="<>0"
End Sub

Kind regards,

Sean

 

[MARK858]

Works fine for me for hiding the 0's. See the file in the link below.

https://app.box.com/s/k034r7971a2hpg7ucx2j8zphibf624go

If you want it all on the one button change Macro1 to

Sub Macro1()
    If ActiveSheet.FilterMode = True Then
        ActiveSheet.ShowAllData
    Else
        ActiveSheet.Range("$C$5:$C$127").AutoFilter Field:=1, Criteria1:="<>0", VisibleDropDown:=False
    End If
End Sub