VBA Macro code questions

ariel20029

Board Regular
Joined
Jun 20, 2013
Messages
97
Hi all,
I found a code to loop through the columns of a pivot table and identify the Columns that add to zero and hide them, but the code is stopping before it completes. The code is stopping at LastCol = Cells(GrTotLR, Columns.Count).End(xlToLeft).Column

and I am not sure why. Also is there any way to add to the if the column value is NULL to hide as well? thanks for any help.
Sharon

Code:
[FONT=Verdana]Sub HideCol()
Dim LR As Long, LastCol As Long, GrTotLR As Long
 
'Find last row on sheet with pivot table
'Assumes column A is best column to use for data population
'(This may or may not be the Pivot Table Grand Total row,
'depending on what else may be in the sheet!)
    With Sheets("WF PIVS")
        LR = Range("A" & Rows.Count).End(xlUp).Row
    End With
 
'Find the Grand Total Row on sheet with pivot table
'Assumes pivot table begins in column A
    With Sheets("WF PIVS")
        Set FndGrTot = Range("A1:A" & LR).Find(What:="Grand Total", LookIn:=xlFormulas, _
            Lookat:=xlWhole, Searchdirection:=xlPrevious, MatchCase:=False, _
            SearchFormat:=False)
             If Not FndGrTot Is Nothing Then GrTotLR = FndGrTot.Row
    End With
 
'Find last column with data populated on Grand Total Row
    With Sheets("WF PIVS")
      [U][B]  LastCol = Cells(GrTotLR, Columns.Count).End(xlToLeft).Column[/B][/U]
    End With
 
'Loop through cells in Grand Total row & hide columns if
'cell value = zero
        For i = 1 To LastCol
            If Cells(GrTotLR, i).Value = "0" Then
                Cells(GrTotLR, i).EntireColumn.Hidden = True
            End If
        Next i
 
MsgBox ("DONE!")
End Sub[/FONT]
 
Last edited by a moderator:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
If you step through the code using F8, when you get to this line
Code:
LastCol = Cells(GrTotLR, Columns.Count).End(xlToLeft).Column
hover your mouse over "LastCol".....does it provide you with the correct last column ???
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top