Hide rows with VBA

[pantelis]

Hi all,

Need some quick help.

When I run a simulation it generates 10 rows of data for years 1987(row1) to
2002(row16) which is placed into sheet2 of the workbook.

I want the macro to look at sheet1,cell A1, say this has year 1995 in the
cell, and when I press the macro button this would hide rows 1987 up to 1994
and therefore display only 1995 to 2002.

So the only variable is cell A1 i.e. 1995

Thanks in advance to anyone's help

Pantelis

 

[Tom Ogilvy]

Sub HideRows()
Rows.Hidden = False

Range(Cells(1,1),ActiveCell.Offset(-1,0)).EntireRow.Hidden = True
End Sub

Assign that to my button (forms toolbar or menu button) or put similar code
in the click event for a commandbarbutton.

 

[pantelis]

Tom,

Thanks again for your quick answers, does work but only for the active cell,
when refering to another cell what do I do
this is soemthing i put together but id does not work

Sub HideRows()

Dim Rownumber As Integer
Sheets("Shee1").Cells(1, 6).Value = Rownumber
Rows.Hidden = False
Range(Cell(4, 1), Cell(Rownumber - 1, 1)).EntireRow.Hidden = True
End Sub

Would appreciate your help
Pantelis

 

[Tom Ogilvy]

Sorry, misread the question.

Sub HideRows()
Dim yr As Long
Dim numRows As Long
yr = Worksheets("Sheet1").Cells(1, 1)
If yr < 1987 Then Exit Sub
numRows = yr - 1987
With Worksheets("Sheet2")
.Rows.Hidden = False
.Cells(1, 1).Resize(numRows).EntireRow.Hidden = True
End With
End Sub