• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automatically Hide rows based on criteria

A

abhi2611

Member
Hi,

Issue - I have a number of sheets in a workbook. Based on the cell value on each sheet I would like to hide the entire row.

Could some one help me with a macro that could do this.

Any help is appreciated.

A.G.
 
Hi abhi2611,

Here is the code that hides the rows in all the sheets if there is a "zero" (you can modify this depending on what value you need)

Code: 
Sub Hide()

Dim wks As Worksheet
On Error Resume Next
For Each wks In ThisWorkbook.Worksheets
    
    With wks
    wks.Select
      LastRow = Range("A65536").End(xlUp).Row          '
                Set Rng = Range("A1:A" & LastRow) 'choose column where value exists
                For Each cell In Rng
                    If cell.Value = "0" Then  'Change the value based on which the rows need to be hidden
                        cell.EntireRow.Hidden = True
                    End If
                Next cell
        End With
Next wks

End Sub

Let me know if this helps.
 
@AIM
Hi!
... if you don't have more than 64K rows or use 2003 version files, and in this last case if the column isn't all filled.
Regards!
 
@AIM

Thank you for the code.

My spreadsheet gets updated on a monthly basis. In other words, the cells that have a zero value might not be zero next month. So, Is it possible to add a few more lines of code which would unhide the rows that are not zero?

Thank you for your time.
A.G.
 
@abhi2611,

Here is the updated code. This would now unhide all the rows and then hide the rows which has zero in Column A.
Code: 
Option Explicit
Sub Hide()
Application.ScreenUpdating = False

Dim wks As Worksheet
Dim Lastrow As String
Dim Rng As Range
Dim cell As Range

On Error Resume Next
For Each wks In ThisWorkbook.Worksheets
   
    With wks
     wks.Select
      Rows.Hidden = False
      Lastrow = Range("A" & Rows.Count).End(xlUp).Row          '
                Set Rng = Range("A1:A" & Lastrow) 'choose column where value exists
                For Each cell In Rng
                    If cell.Value = "0" Then  'Change the value based on which the rows need to be hidden
                        cell.EntireRow.Hidden = True
                    End If
                Next cell
    End With
Next wks

MsgBox "All Rows containing Zero in Column A have been hidden", vbInformation, "Information"

Application.ScreenUpdating = True
End Sub
Hope this helps.

Regards!!
 
Hi,

I see this code works for each sheet. What if I want to have this code working for only one sheet?
What would be the procedure

Regards,
Fadil
 
Hi Fadil ,

See this :
Code: 
Sub Hide()
    Dim Lastrow As String
    Dim Rng As Range, cell As Range

    Application.ScreenUpdating = False

    On Error Resume Next
     
    With ThisWorkbook.Worksheets("Sheet1")
        .Rows.Hidden = False
        .Cells(.Rows.Count, 1).Select
        If ActiveCell.Value = "" Then
            Lastrow = Selection.End(xlUp).Row
        Else
            Lastrow = Rows.Count
        End If
       
        Set Rng = Range("A1:A" & Lastrow) 'choose column where value exists
        For Each cell In Rng
            If cell.Value = "0" Then  'Change the value based on which the rows need to be hidden
                cell.EntireRow.Hidden = True
            End If
        Next cell
    End With

    MsgBox "All Rows containing Zero in Column A have been hidden", vbInformation, "Information"

    Application.ScreenUpdating = True
End Sub
Replace Sheet1 in the above code by the name of your worksheet.

Narayan
 
Hi Narayan,

Thank you for your quick response. I tried the code and all I can get it to do is unhide the current hidden rows and that's it, it doesn't hide the rows where values says "False" although the code doesn't get any debug notice when running

Regards,
Fadil
 
This is the code where I am trying to hide the rows where the cell value says "False"
I have about 5008 rows

I have another code but it is working very very slow
Sub Hide()
Dim ProjectScheduleDetails As Worksheet
Dim Lastrow As String
Dim Rng As Range, cell As Range

Application.ScreenUpdating = False

Set ProjectScheduleDetails = ThisWorkbook.Worksheets("ProjectScheduleDetails")
ProjectScheduleDetails.Activate
Range("AY9").Select
Range(Selection, Selection.End(xlDown)).Select
Range("AY9:AY5003").Select
Selection.Copy
Range("AZ9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AY9").Select

On Error Resume Next
With ThisWorkbook.Worksheets("ProjectScheduleDetails")
.Rows.Hidden = False
.Cells(.Rows.Count, 1).Select
If ActiveCell.Value = "" Then
Lastrow = Selection.End(xlUp).Row
Else
Lastrow = Rows.Count
End If

Set Rng = Range("AZ1:AZ" & Lastrow) 'choose column where value exists
For Each cell In Rng
If cell.Value = "False" Then 'Change the value based on which the rows need to be hidden
cell.EntireRow.Hidden = True
End If
Next cell
End With
Application.ScreenUpdating = True

End Sub
 
This one is the code that gets the job done, but it is very VERY slow

Sub TEST()
'
' TEST Macro
Dim ProjectScheduleDetails As Worksheet
Dim cell As Range

Application.ScreenUpdating = False
Application.EnableEvents = False
For Each cell In Range("AY1:AY5008")
If cell.Value = "FALSE" Then
cell.EntireRow.Hidden = True
Else
cell.EntireRow.Hidden = False
End If
Next
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub
 
You must log in or register to reply here.
Back
Top