Find Grand Total Row (Dynamic) and Hide Columns with "0" in Grand Total Row

[jwb1012]

Hello, I am trying to come up with some code to find the row with "Grand Total" in column B and set that as LastRow. The grand total row will change (ex: move from row 20 to 30, when more data is added), so I need this to be dynamic which is where I'm kind of struggling.

Then I want to search LastRow from "B:T" and if the value is "0" then I would like to hide the entire column.

Any thoughts?

 

[jwb1012]

Here is what I've got so far, but I need the range to be dynamic from $C45:$U45 to be set by finding the grand total row.

Sub HideColumn()
    Dim col As Range, GrandTotal As Range
     
    Set GrandTotal = Range([U][I][B]"C45:U45")[/B][/I][/U]
    For Each col In GrandTotal
        If col.Value = "-" Then
            col.EntireColumn.Hidden = True
        End If
    Next col
End Sub

 

[Trebor76]

Hi jwb1012,

Just check if the total column comes to zero or not and then hide that column if does so no need check the grand total formula i.e.

Option Explicit
Sub Macro1()

    Dim lngLastCol As Long
    Dim lngMyCol As Long
    Dim strMyCol As String
    
    Application.ScreenUpdating = False
    
    On Error Resume Next 'Account for there being no data on the tab
        lngLastCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        If lngLastCol >= 3 Then 'Only work if there's 3 or more columns
            For lngMyCol = lngLastCol To 3 Step -1
                strMyCol = Left(Cells(1, lngMyCol).Address(True, False), Application.WorksheetFunction.Search("$", Cells(1, lngMyCol).Address(True, False)) - 1)
                If Evaluate("SUM(" & strMyCol & ":" & strMyCol & ")") = 0 Then
                    Columns(strMyCol).Hidden = True
                Else
                    Columns(strMyCol).Hidden = False
                End If
            Next lngMyCol
        End If
    On Error GoTo 0
    
    Application.ScreenUpdating = True
    
End Sub

Note you need work backwards when hiding or deleting.

HTH,

Robert

 

[Trebor76]

In case you absolutely need to find the text "Grand Total" (not case sensitive) in column B and work from there, try this:

Option Explicit
Sub Macro2()

    Dim rngFound    As Range
    Dim lngMyRow    As Long
    Dim lngLastCol  As Long
    Dim lngMyCol    As Long
    Dim strMyCol    As String
    
    With Range("B:B")
        Set rngFound = .Find(What:="Grand Total", After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, MatchCase:=False)
        If Not rngFound Is Nothing Then
            lngMyRow = rngFound.Row
        Else
            MsgBox "The text ""Grand Total"" (not case sensitive) was not found in Col. B"
            Exit Sub
        End If
    End With
    
    Application.ScreenUpdating = False
    
    On Error Resume Next 'Account for there being no data on the tab
        lngLastCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        If lngLastCol >= 3 Then 'Only work if there's 3 or more columns
            For lngMyCol = lngLastCol To 3 Step -1
                strMyCol = Left(Cells(1, lngMyCol).Address(True, False), Application.WorksheetFunction.Search("$", Cells(1, lngMyCol).Address(True, False)) - 1)
                If Val(Range(strMyCol & lngMyRow)) = 0 Then
                    Columns(strMyCol).Hidden = True
                Else
                    Columns(strMyCol).Hidden = False
                End If
            Next lngMyCol
        End If
    On Error GoTo 0
    
    Application.ScreenUpdating = True

End Sub

Robert