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

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
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?
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
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.
Code:
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,676
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.

Code:
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
 
Last edited:

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,676
In case you absolutely need to find the text "Grand Total" (not case sensitive) in column B and work from there, try this:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,677
Messages
5,524,225
Members
409,566
Latest member
santoshsj

This Week's Hot Topics

Top