MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Hiding rows in a worksheet.


Posted by Chris Rock on July 26, 2001 8:47 AM

I have a worksheet that I update every day. Each day, I paste in 10 to 40 new rows.

I have a "calculation matrix" that starts on row 2000, because my data will never extend that far down.

Each day, in order to hide the unnecessary rows, I simply activate the cell two below where the data ends (leaving a blank row), and highlight and hide all the rows between this row and row 1999.

I can't seem to use the macro recorder to record a macro to do this, because the active cell position is different every day, because data is added.

Here's what I think the macro has to do:
1. Start at cell A1.
2. Go to the end of the data (I would use ctrl+down to do this).
3. Go two cells down (in order to leave a blank row)
4. Highlight all the cells between this position and row 1999. (Currently, I use ctrl+shift+down, then I press shift+up once because it hightlights through row 2000.)
5. Hide highlighted rows. (I use ctrl+9 for this).
6. Activate cell A2000.

Can anyone help me with the code for this? Thanks in advance. My current process doesn't take too long, but it's difficult to explain to others.


Posted by faster on July 26, 2001 8:56 AM

try this

Sub HideRows()
Range("A1").Select
Selection.End(xlDown).Select
Selection.Offset(2, 0).Select

Dim Anchor
Anchor = ActiveCell.Address

Range(Anchor, "A1999").EntireRow.Select
Selection.EntireRow.Hidden = True
Range("A2000").Select

End Sub

Posted by Chris Rock on July 26, 2001 9:20 AM

Thank you, thank you.

This is exactly what I needed. I appreciate your help.