collapse an expand rows using VB

[soinx]

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?

 

[Guest]

Hi there

The code below works fine however I don't know if it is exactly what you
need. Because when a Row is set to 0 you cannot access it anymore except if
the height is changed again manually.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
If Target.Value = 100 Then
Rows(Target.Row).RowHeight = 0
Else
Rows(Target.Row).RowHeight = 17
End If
End If
End Sub

Kind regards,
Alex

 

[Jacob Skaria]

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

 

[Atishoo]

I used a for next loop as follows:

With Worksheets(1).Range("C:C")
Set c = .Find(100, LookIn:=xlValues)
If Not c Is Nothing Then
c.RowHeight = 17
firstAddress = c.Address
Do
c.RowHeight = 0

Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
End With

 

[Stefi]

Try this event macro:
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Dim ccell As Range
shend = Range("A1").SpecialCells(xlCellTypeLastCell).Row
For Each ccell In Range("C1:C" & shend)
ccell.EntireRow.Hidden = (ccell = 100)
Next ccell
Application.EnableEvents = True
End Sub


Post if you need help to install it!
Regards,
Stefi


„soinx†ezt írta:

 

[soinx]

thanks for the input. I tried Jacobs string and i got it to work

I just altered the total collaps to a height of 1 to avoid problems of
expanding again.

Thanks again ;-)

 

[soinx]

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]

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