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

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

"soinx" <(E-Mail Removed)> schrieb im Newsbeitrag
news37167EA-EEC1-406C-8AE8-(E-Mail Removed)...
>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?

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?

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?

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?

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?

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?

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?