Replies

thanks steve, I thought you left for the day.

Private lngMaxRow As Long

Private Sub EXEC_BTN_Click()
HiliteMeAndSiblings ActiveCell
End Sub

Sub HiliteMeAndSiblings(oCell As Range)
lngMaxRow = Range(“I1000”).End(xlUp).Row

Dim i As Long
‘oCell.Offset(0, -1).Resize(1, 2).Font.ColorIndex = 5
oCell.Offset(0, -1).EntireRow.Hidden = False
For i = 9 To lngMaxRow
Debug.Print i
If ActiveCell “” Then
Range(“H5:I” & lngMaxRow).EntireRow.Hidden = True
End If
If Range(“H” & i) = oCell Then
Range(“I” & i).EntireRow.Hidden = False
End If
Next i

End Sub
Private Sub CommandButton1_Click()
With Sheet1
ActiveWorkbook.RefreshAll
End With
End Sub

How come it does not find all the parents and children?

I did finally get it to refresh by doing the following:

Private Sub CommandButton1_Click()
With Sheet1
ActiveWorkbook.RefreshAll
End With
End Sub

Hi Hans did steve leave for the day? Is there anyway I can just look for the lastrow instead of going through all the rows excel has? It takes it too long to calculate everything.

sorry steve but INTERSECT is not in the EXCEL 2000 book. Did you mean INTERCEPT?

this is what I have so far:

Private Sub EXEC_BTN_click()

lngMaxRow = Range(“I65536”).End(xlUp).Row
If Range(“H2:I” & lngMaxRow) = “” Then Range(“H4:I” & lngMaxRow).EntireRow.Hidden = False
End Sub

ok I’m lost. I still cannot get this to bring back all my rows.

Ok I got it to actually work with the directions you stated. But now how do I bring them all back so that I can click on another Child or parent? Is there a way to make it so that I can just click on the spreedsheet anywhere and it brings them all back?

This is what I have so far.
Private lngMaxRow As Long

Private Sub EXEC_BTN_Click()

lngMaxRow = Range(“I65536”).End(xlUp).Row
Range(“H2:I” & lngMaxRow).Font.ColorIndex = xlColorIndexAutomatic
HiliteMeAndSiblings ActiveCell
End Sub

Sub HiliteMeAndSiblings(oCell As Range)
Range(“H4:I” & lngMaxRow).EntireRow.Hidden = True

Dim i As Long
‘oCell.Offset(0, -1).Resize(1, 2).Font.ColorIndex = 5
oCell.Offset(0, -1).EntireRow.Hidden = False
For i = 2 To lngMaxRow
Debug.Print i
If Range(“H” & i) = oCell Then
Range(“I” & i).EntireRow.Hidden = False
End If
Next i
End Sub

that sounds great, But remember one thing Newbie here. I’m still in school learning this stuff.

OK something went wrong. It’s only showing the row that I clicked on and not all parents and children in any other rows.

I think I figured it out. Thank you so much. Now one last question. Is there a way to speed up that process. Because I have over 800 rows of data and it takes a few minutes for it to actually work. If not I will take what I can get. Thank you SOOOOOO much once again.

So where would I place them at in my code. I tried and got an error saying subscript out of range.

Private lngMaxRow As Long

Private Sub EXEC_BTN_Click()

lngMaxRow = Range(“I65536”).End(xlUp).Row
Range(“H2:I” & lngMaxRow).Font.ColorIndex = xlColorIndexAutomatic
HiliteMeAndSiblings ActiveCell
End Sub

Sub HiliteMeAndSiblings(oCell As Range)
Dim i As Long
oCell.Offset(0, -1).Resize(1, 2).Font.ColorIndex = 5
For i = 2 To lngMaxRow
Debug.Print i
If Range(“H” & i) = oCell Then
HiliteMeAndSiblings Range(“I” & i)
End If
Next i
End Sub

Ok, I spent most of my weekend looking through the book, It does not state anywhere what kind of code I need to use to have the rows hide or unhide. Besides doing it manually. What I’m looking for is when the Parent and Children are all selected I need only those rows to appear. and when the user clicks on them again all rows appear again. I need help on this anybody. Please.

I would like rows where I find any reference or child/parent of what I’m looking for to show automatilcally only. another words hide all the other rows that I do not need.