View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Jacob Skaria Jacob Skaria is offline
external usenet poster
 
Posts: 8,520
Default collapse an expand rows using VB

Note the below changes..

Sub HideRows()
Dim lngRow As Long
Application.ScreenUpdating = False
Rows(23 & ":" & Cells(Rows.Count, 3).End(xlUp).Row).RowHeight = 17
For lngRow = 23 To Cells(Rows.Count, 3).End(xlUp).Row
If Range("C" & lngRow) = 100 Then Rows(lngRow).RowHeight = 0
Next
Application.ScreenUpdating = True
End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


"soinx" wrote:

Is it possible to make the code only look at row numbers larger than 22?
Otherwise it messes up my design at the top of the worksheet.

"Jacob Skaria" wrote:

Try the below macro..which works on the active sheet

Sub HideRows()
Dim lngRow As Long
Application.ScreenUpdating = False
Rows(2 & ":" & Cells(Rows.Count, 3).End(xlUp).Row).RowHeight = 17
For lngRow = 1 To Cells(Rows.Count, 3).End(xlUp).Row
If Range("C" & lngRow) = 100 Then Rows(lngRow).RowHeight = 0
Next
Application.ScreenUpdating = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"soinx" wrote:

I want to collapse and expand certain rows in a worksheet. The rows that
should be collapsed (height=0) or expanded (height=17) are determined by the
value of a certain cell in this row. In this case it is the value of the cell
in the C-column that will termine whether or not the row height should be set
to 0 or 17. If the cell value is "100", the height should be set to 0, and if
the cell value is different from 100 the height should be set to 17.

I am a bit lost here. Can anyone help me with this?