Excel VBA code to return used Column Count from the row of an ActiveCell (1 Viewer)

Rx_ · Apr 6, 2017

First Prototype - looking for code to count the number of columns in a specific Row starting at either column H or L.
Will use that result in place of hard coded 30 in
For HideColumnCount = 0 To 30

Note: just in case a cell is empty - exit the loop.

Not shown is custom Sort and Grouping that creates a Tree View.
The Tree View is Data Dependent so the HideStartColumn can vary.
The columns are also data dependent.

Purpose of the code: when a column has a Grand Total of zero, hide the column.

Code runs from MSAccess - the reference to the Excel being modified is ObjXl = Excel.Application

Code:

If Grouping = "Condensed" Then
    HideStartColumn = "H"
   Else
    HideStartColumn = "L"
   End If
    ' find grand total in A
    ObjXL.Columns("A:A").Select
    ObjXL.Selection.Find(What:="Grand Total", After:=ObjXL.ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        GrandTotalSumRow = ObjXL.ActiveCell.Row

                        ObjXL.Range(HideStartColumn & GrandTotalSumRow).Select
                        'Range("H" & TotalSumRow).Select
                        For HideColumnCount = 0 To 30 ' loop prevents error to infinity, but code will stop at just after last column that is empty
                        ObjXL.ActiveCell.Offset(0, 1).Range("A1").Select
                        'Debug.Print "offsetcount " & ColumnCount & " cell value "; ActiveCell.Value & " activecell Address " & ActiveCell.Address
                            If ObjXL.ActiveCell.Value <> Empty Or ObjXL.ActiveCell.HasFormula Then    ' does cell have value or formula
                                If ObjXL.ActiveCell.HasFormula Then
                                    If ObjXL.ActiveCell.Value = 0 Then  ' candidate for hiding
                                        DoEvents
                                         ObjXL.ActiveCell.EntireColumn.Hidden = True
                                        End If
                                    End If
                                Else
                                    Exit For ' Active cell is empty, exit loop
                                End If
                        Next HideColumnCount
                    ObjXL.Range("A1").Select

Excel VBA code to return used Column Count from the row of an ActiveCell (1 Viewer)

Rx_

Nothing In Moderation

Similar threads

Users who are viewing this thread