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

SeanOZ

New Member
Joined
Oct 31, 2019
Messages
13
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,187
Office Version
365, 2010
Platform
Windows, Mobile
Wouldn't it be more efficient to use Autofilter either manually or via VBA?
 

SeanOZ

New Member
Joined
Oct 31, 2019
Messages
13
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,187
Office Version
365, 2010
Platform
Windows, Mobile
Code:
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.
 
Last edited:

SeanOZ

New Member
Joined
Oct 31, 2019
Messages
13
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,187
Office Version
365, 2010
Platform
Windows, Mobile
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

Code:
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
 
Last edited:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,959
Messages
5,508,409
Members
408,682
Latest member
alifarhat

This Week's Hot Topics

Top