Results 1 to 3 of 3
2011-06-08, 13:17 #1
- Join Date
- Jun 2004
- Whistler, Br. Columbia, Canada
- Thanked 0 Times in 0 Posts
auto-hide the same rows over many sheets
I have a spreadsheet with many sheets where I want to hide the same rows on all but the first 3 sheets. Sheet 1 is the summary page - on this sheet I enter a date range (also have a pair of cells that convert that date range to row numbers). On sheets 4 to end (the sheet names that should be affected can be determined from the summary page if necessary), I want to hide the rows that don't fall within the date range. For ease of coding and speed of execution it would be fine to only hide the rows that fall before the beginning of the date range. All sheets will have the same rows hidden. I'd like to have the hiding happen on a change in the date range. Expanding the range would have to unhide previously hidden rows.
How do I do this??
I found this code:
BeginRow = 1
EndRow = 100
ChkCol = 3
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value < 5
Then Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else Cells(RowCnt, ChkCol).EntireRow.Hidden = False
which seems like a good place to start but I'm not sure how to go from here...
2011-06-09, 04:02 #2
- Join Date
- Dec 2000
- Burwash, East Sussex, United Kingdom
- Thanked 191 Times in 177 Posts
Not a lot to go on there, but basically something like:
Sub HURows() DIm n as long, BeginRow as Long, EndRow as Long With Sheets("Summary") ' change cells as required BeginRow = .range("B1").value EndRow = .range("B2").value End with ' Loop through sheets 4 to last For n = 4 to Sheets.Count With Sheets(n) ' Unhide all rows .Usedrange.Entirerow.Hidden = False ' Hide all rows before BeginRow (assuming it's not 1) If BeginRow > 1 then .Rows(1).Resize(BeginRow - 1).Hidden = True ' Hide all rows after EndRow If EndRow < .Rows.Count Then .Rows(EndRow + 1).Resize(.Rows.Count - EndRow).Hidden = True End With Next n End SubRegards,
Microsoft MVP - Excel
2011-06-09, 11:43 #3
- Join Date
- Jan 2001
- Redcliff, Alberta, Canada
- Thanked 5 Times in 5 Posts
Here's a non-code method that will work if the rows are the same in every sheet.
Group the sheets together by clicking on the first sheet tab and shift + clicking on the last sheet tab. The sheet tabs will change colour to confirm the sheets are grouped. Now hide your rows. Right-click on any sheet tab and select ungroup sheets.
When sheets are grouped together, formatting actions are applied to all sheets simultaneously.[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile