Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i expand and collapse rows | New Users to Excel | |||
Expand/Collapse Buttons for hidden rows or colums | Excel Discussion (Misc queries) | |||
How do I move the expand/collapse for rows and columns in Excel? | Excel Worksheet Functions | |||
Programming Outlines (expand/collapse rows) in Excel | Excel Programming | |||
Expand/Collapse Rows | Excel Worksheet Functions |