VBA referencing the wrong rows, references, and columns 1

[clouddog9]

Ok, here is the problem. It has been frustrating me for a LONG time (by long time I mean a few hours). I have a macro that hides every rows that ends with a 2 through 0 (e.g. 2, 3, 4, 5, 6, 7, 8, 9, 10, 12, 13, 14, 15, 16, 17, 18, 19, 20, etc) the reason for skipping lines that begin with one is because there is a summary on every line that begins with 1 (e.g. 11, 21, 31, etc). I have another macro that follows a hyperlink. This follow hyperlink will unhide rows 2 through 10 (and every increment there after respective which line that ends in one that was clicked). Everything was working fine, but all the sudden when I run the follow hyperlink macro the wrong section unhides/hides. When I cycle through the variables, the variable that references the row contains the correct row number, but excel/vba chooses an incorrect row. I would say randomly, except it is almost not random. I placed watches for these variables and if the variable in vba for the row to hide says 11, excel/vba will choose 21. Here's another screwy thing, if the variable says 81, excel/vba will choose 161. It is bizzare and irritating. It seems that excel/vba is wanting to take the row reference drop of the last digit, double the remaining digits the concatenate the dropped of digit back onto the row reference. I have done everything I can think of from restarting excel, to restarting the computer to copying the macros to another workbook and starting from scratch. I really have no idea where to go from here. Anybody got some suggestions?

 

[PHV]

Anybody got some suggestions?
Well, without seeing any code ...

 

[clouddog9]

I guess my explanation was not clear enough (which I kind of thought it wouldn't be):

Option Explicit
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    With ActiveCell
        Select Case .Column
            Case 2                
                Dim varStart As Integer
                Dim varFinish As Integer
                Dim varRollupStart As Integer
                Dim varRollupFinish As Integer
                Dim varSubordHdr As Integer
                Dim varHeaderCon As Integer
                Dim strRange As String
                Dim strCellValue As String
                Dim rngToggle As Range
                Dim rng2ndToggle As Range
                Dim rngSubOrdToggle As Range
                Dim strDCodeName As String
                Select Case .Interior.Color
                    Case RGB(255, 255, 255)
                        If Right(.Value, 8) <> "(ROLLUP)" Then
                            varStart = .Row - 9
                            varFinish = .Row - 1
                            strDCodeName = StrConv(WorksheetFunction.Substitute(.Value, Left(.Value, 11), ""), vbProperCase)
                            Set rngToggle = .Rows(varStart & ":" & varFinish)
                            Select Case rngToggle.EntireRow.Hidden
                                Case True
                                    rngToggle.EntireRow.Hidden = False
                                    .Hyperlinks(1).ScreenTip = "Collapse " & strDCodeName
                                Case False
                                    .Rows("2:10").EntireRow.Hidden = True
                                    .Hyperlinks(1).ScreenTip = "Expand " & strDCodeName
                            End Select
                        Else
                            varStart = .Row - 9
                            varFinish = .Row - 1
                            Set rngToggle = Rows(varStart & ":" & varFinish)
                            Do
                                If varHeaderCon = 0 Then varHeaderCon = 10
                                varSubordHdr = .Row - varHeaderCon
                                varRollupStart = varSubordHdr - 9
                                varRollupFinish = varSubordHdr - 1
                                If rng2ndToggle Is Nothing Then
                                    Set rng2ndToggle = .Rows(varRollupStart & ":" & varRollupFinish)
                                    Set rngSubOrdToggle = .Rows(varSubordHdr & ":" & varSubordHdr)
                                Else
                                    Set rng2ndToggle = Union(rng2ndToggle, .Rows(varRollupStart & ":" & varRollupFinish))
                                    Set rngSubOrdToggle = Union(rngSubOrdToggle, .Rows(varSubordHdr & ":" & varSubordHdr))
                                End If
                                varHeaderCon = varHeaderCon + 10
                            Loop Until Range("B" & .Row - varHeaderCon).Interior.Color <> RGB(153, 153, 255)
                            varHeaderCon = varHeaderCon - varHeaderCon + 10
                            Select Case rngToggle.EntireRow.Hidden
                                Case True
                                    rngToggle.EntireRow.Hidden = False
                                    rngSubOrdToggle.EntireRow.Hidden = False
                                    strDCodeName = StrConv(WorksheetFunction.Substitute(.Value, Left(.Value, 11), ""), vbProperCase)
                                    .Hyperlinks(1).ScreenTip = "Collapse " & strDCodeName
                                Case False
                                    Do
                                        With Range("B" & .Row - varHeaderCon)
                                            strDCodeName = StrConv(WorksheetFunction.Substitute(.Value, Left(.Value, 11), ""), vbProperCase)
                                            .Hyperlinks(1).ScreenTip = "Expand " & strDCodeName
                                        End With
                                        varHeaderCon = varHeaderCon + 10
                                    Loop Until Range("B" & .Row - varHeaderCon).Interior.Color = RGB(255, 255, 255)
                                    rngToggle.EntireRow.Hidden = True
                                    rngSubOrdToggle.EntireRow.Hidden = True
                                    rng2ndToggle.EntireRow.Hidden = True
                                    strDCodeName = StrConv(WorksheetFunction.Substitute(.Value, Left(.Value, 11), ""), vbProperCase)
                                    .Hyperlinks(1).ScreenTip = "Expand " & strDCodeName
                            End Select
                        End If
                    Case Else
                        varStart = .Row - 9
                        varFinish = .Row - 1
                        Set rngToggle = .Rows(varStart & ":" & varFinish)
                        Select Case .Interior.Color
                            Case RGB(0, 0, 128)
                                strDCodeName = StrConv(WorksheetFunction.Substitute(.Value, "SUMMARY ", ""), vbProperCase)
                            Case RGB(153, 153, 255)
                                strDCodeName = StrConv(WorksheetFunction.Substitute(.Value, Left(.Value, 11), ""), vbProperCase)
                        End Select
                        Select Case rngToggle.EntireRow.Hidden
                            Case True
                                rngToggle.EntireRow.Hidden = False
                                .Hyperlinks(1).ScreenTip = "Collapse " & strDCodeName
                            Case False
                                rngToggle.EntireRow.Hidden = True
                                .Hyperlinks(1).ScreenTip = "Expand " & strDCodeName
                        End Select
                End Select
            Case 6
            Case 9
            End Select
    End With    
End Sub

 

[clouddog9]

I personally do not think it has anything to do with the code though. I am wondering if it is an excel thing.

 

[clouddog9]

Here is a simplier example:

Rows("2:10").EntireRow.Hidden = True

Instead of hidding rows 2 through 10, it hides rows 12 through 20.

 

[PHV]

In fact, you're talking about ActiveCell.Rows("2:10"), don't you ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[clouddog9]

Wow, that took care of it. I offically feel stupid.

 

[SkipVought]

every rows that ends with a 2 through 0 (e.g. 2, 3, 4, 5, 6, 7, 8, 9, 10, 12, 13, 14, 15, 16, 17, 18, 19, 20, etc)...

Do you mean, every row that does not end with a ONE (e.g. 1, 11, 21...)???

lines that begin with one is because there is a summary on every line that begins with 1 (e.g. 11, 21, 31, etc).

Do you mean rows that END with a ONE???

I want to be sure that I am understanding your kind of hard to follow explaination.

So you want to hide a range of rows, but be sure that every row that ends with a ONE is visible?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[clouddog9]

You get a star for help.

 

[clouddog9]

I WAS referencing ActiveCell.Row(...). After changing this the problem was solved.

@Skip, I was furstrated and trying to get the thoughts in my head on paper. When I get stressed my brain backlashes like a cheap fly fishing reel.

 

[SkipVought]

I had worse backlashes on a bait casting reel. Either that or thumb burn. ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}