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

#### jwb1012

##### Board Regular
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?

### 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
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
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
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
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
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