collapse an expand rows using VB
 

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?

collapse an expand rows using VB
 

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

------------------------------------
Excel-Spezialist
www.excelspezialist.ch
------------------------------------

"soinx" schrieb im Newsbeitrag
...
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?

collapse an expand rows using VB
 

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?

collapse an expand rows using VB
 

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

"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?

collapse an expand rows using VB
 

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:

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?

collapse an expand rows using VB
 

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" 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?

collapse an expand rows using VB
 

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?

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?