Hiding certain rows in a worksheet
Posted by Chris Rock on April 24, 2001 1:00 PM
I have a data table that gets updated every week. The most recent data is at the bottom of the table.
It may become necessary to only see the past 20 weeks worth of data, so I'd need to hide any rows that are "older" than 20 weeks.
I used the macro recorder to record a macro to do this, but it won't work. These are the keyboard commands I used when recording the macro:
Ctrl+Home (To bring me to cell A1).
Ctrl+Down Arrow, Ctrl+Down Arrow (to skip to the last row of data).
Up Arrow X 20 (To bring me to the 21st row from the bottom).
Shift+SpaceBar (To highlight the entire 21st row from the bottom).
Ctrl+Shift+Up Arrow (To highlight all the rows from the 21st row up to the Column Headings Row [column A]).
Ctrl+9 (To Hide the rows).
Ctrl+Home (To bring me back to A1).
When I record this as a macro, it doesn't work. This is what the code looks like:
Range ("A1").Select
Selection.End(x1 Down).Select
Selection.End(x1 Down).Select
ActiveCell.Offset(-23,0).Rows(1:4).EntireRow.Select
ActiveCell.Offset(-20,0).Range("A1").Activate
Selection.EntireRow.Hidden = True
Range.("A1").Select
Is there any better way to do this? In my sheet that I used as an example, I had 4 extra rows that I needed to hide, but there may be more or less.
I know very, very little about VBA, but I do understand what is happening in this code. I'd like to be able to write the macro rather than record it.
Can anyone shed some light on this?
Posted by Jerid on April 24, 2001 1:22 PM
Hi Chris
How do you determine the age of the line, do you have the date in one of the columns or does the line number represent the week?
Posted by Chris Rock on April 24, 2001 1:24 PM
I have a number in Column A, and also a Date in Column B. I can do whatever is easier.
Posted by Chris Rock on April 24, 2001 1:27 PM
As you can see by my original post, though, I TRIED just to count 20 rows up from the bottom. That didn't work, as it can be different each time.
Posted by Jerid on April 24, 2001 1:46 PM
You can use this code to start at cell A1 and look at each row to see if the date in column B is older than the date you type into cell C1 if it is then hide the row.
Sub HideOldData()
Application.Range("A1").Select
Do While ActiveCell.Value <> ""
If ActiveCell.Offset(0, 1).Value < Application.Range("C1").Value Then
ActiveCell.EntireRow.Hidden = True
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub
I just use cell C1 to store the oldest date you would like to be visible
Posted by Chris Rock on April 24, 2001 1:57 PM
THANKS! I will give this a try. This might be just what I need.
Posted by David Hawley on April 24, 2001 4:01 PM
Hi Chris
Jerids code will no doubt work! But as I dislike loops you could try the Autofilter method, it will run a lot quicker than a Loop.
Sub HideRows()
Dim SCriteria As String
Dim RHidden As Range
Dim Rcell As Range
' Written by OzGrid Business Applications
'www.ozgrid.com
'''''''''''''''''''''''''''''''''''
' Hide rows based on a criteria
''''''''''''''''''''''''''''''''''
SCriteria = Range("C1")
ActiveSheet.AutoFilterMode = False
Range("A1").AutoFilter
Range("A1").AutoFilter Field:=1, Criteria1:=">" & SCriteria
Set RHidden = ActiveSheet.UsedRange.Columns(1).SpecialCells _
(xlCellTypeVisible)
ActiveSheet.AutoFilterMode = False
RHidden.EntireRow.Hidden = True
End Sub
Dave
OzGrid Business Applications
Posted by Chris Rock on April 26, 2001 2:31 PM
Hiding only certain rows in a worksheet, part 2....
Jerids code will no doubt work! But as I dislike loops you could try the Autofilter method, it will run a lot quicker than a Loop. Sub HideRows() Field:=1, Criteria1:=">" & SCriteria
Dave,
Thanks for the suggestion, but this is not gonna work for me, because it hides the "Total" row, because it doesn't have the exact same fields as the other rows do. It also hides my column heading row, but I was able to adjust the macro and un-hide it after it was hidden.
To restate my original issue:
I have a data that is updated weekly with a new row being added (via another macro) for each new week. Charts are made mapped to this data, and I need to only show the latest 20 weeks worth of data. I'd like a macro that can hide the rows of data that are more than 20 weeks "older" than the last row that was entered, (the rows each contain a date) or counts up 20 rows from the last one and hides all those above (except for the first row, which are my column headings).
The rows represent weeks of data.
There are column headings in row 1, and after the last row containing data, there is a blank row followed by a "totals" row.
Any other ideas?
I've tried recording my own macro, but I can't get it to work.
