Hide rows with zero value in Multiple sheets
Solved/Closed
Related:
- Hide rows with zero value in Multiple sheets
- Conditional hide rows in multiple sheets ✓ - Forum - Excel
- Copy Rows to multiple sheets based on drop down validation ✓ - Forum - Excel
- Split excel sheet into multiple sheets based on rows ✓ - Forum - Excel
- Excel delete same row in multiple sheets - Forum - Excel
- How to apply a formula to multiple sheets in excel - Guide
1 reply
rizvisa1
Apr 12, 2010 at 09:31 AM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
Apr 12, 2010 at 09:31 AM
Objective:
To filter /hide the rows on all sheet for a given value.
Assumptions:
1. The location of the column is same on every sheet
3. Filter needs to be applied on all sheets
3. Sheets are not hidden
4. All Sheets have at least one cell filled
STEPS
1. Read the assumptions
2. Make a back up copy
3. Press ALT + F11 and insert a new module
4. Paste the code and run it
CODE:
To filter /hide the rows on all sheet for a given value.
Assumptions:
1. The location of the column is same on every sheet
3. Filter needs to be applied on all sheets
3. Sheets are not hidden
4. All Sheets have at least one cell filled
STEPS
1. Read the assumptions
2. Make a back up copy
3. Press ALT + F11 and insert a new module
4. Paste the code and run it
CODE:
Sub HideRows() Dim Sheet As Object Dim filterFor As Variant Dim iFilterCol As Integer iFilterCol = 3 'apply filter on 3 col filterFor = InputBox("Enter the value to be filtered out", "Filter out") For Each Sheet In Sheets Sheet.Select If ActiveSheet.AutoFilterMode Then Cells.Select Selection.AutoFilter End If Cells.Select If ActiveSheet.AutoFilterMode = False Then Selection.AutoFilter End If Selection.AutoFilter Field:=iFilterCol, Criteria1:="<>" & filterFor, Operator:=xlAnd Next End Sub
Jul 10, 2013 at 01:49 PM
Worked perfect......