Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows with VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows with VBA
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. -- Regards, Tom Ogilvy "pantelis" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows with VBA
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" wrote in message ... 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. -- Regards, Tom Ogilvy "pantelis" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows with VBA
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 -- Regards, "pantelis" wrote in message ... 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" wrote in message ... 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. -- Regards, Tom Ogilvy "pantelis" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Group rows (or hide rows) like in MS Project | Excel Worksheet Functions | |||
Macro code to hide rows and not calculate hidden rows | Excel Discussion (Misc queries) | |||
Enabling option „Format rows“ to hide/unhide rows using VBA-code? | Excel Discussion (Misc queries) | |||
cut rows without cutting hide rows | Excel Discussion (Misc queries) | |||
Hide Rows - copy and paste only rows that show | Excel Worksheet Functions |