collapse an expand rows using VB

  • Thread starter Thread starter soinx
  • Start date Start date
S

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?
 
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
 
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
 
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
 
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:
 
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 ;-)
 
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.
 
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
 
Back
Top