Filling Excel remote automation from Recordset - Hide columns if all values are zero (1 Viewer)

[Rx_]

SQL Server Linked Table (a view) --> MS Access --> Excel
MS Access report generator using Excel with remote automation.
Will be using a Recordset to move data (copy from recordset) into an Excel template.

Using the recordset, will evaluate 27 columns. On average, about half of them will have no data.
The idea is after the data is in Excel, hide the columns with no data.
For example: if Column F, J, M have no data, hide them.

The rows rarely go past 1,000.
Trying to decide for example to use SQL and create an Array of the empty columns to use in vba excel remote automation.
Or, to use something like a Sum function and loop through 27 columns to make the determination.
Or... Maybe someone else has an idea?

Set rs = DB.OpenRecordset(TableName, dbOpenSnapshot)
    rs.MoveLast
    rs.MoveFirst
    rsRowCount = rs.RecordCount
    rsColumnCount = rs.Fields.Count
 ' Function - evaluate empty columns in rs and create array
    
    ObjXL.Workbooks(1).Worksheets(TabName).Select ' 
     ObjXL.Range(StartLocation).Select
    ObjXL.Workbooks(1).Worksheets(TabName).Range(StartLocation).CopyFromRecordset rs, rsRowCount, rsColumnCount
    DoEvents
    ' Data input keeps turning on Autowrap in some cases - fix that now
    
    TotalSumRow = rsRowCount + 6 + 1  ' add start location here
    ObjXL.Range("A" & TotalSumRow).Select
 ' use Function array to hide Objxl.Columns( xxxx) columns here

 

[Rx_]

? objxl.Application.WorksheetFunction.CountA(objxl.Range("L6:L20"))
Hard coding the Range works perfectly.
Thought the Activecell with a range might work too. Stepping through the code, it actually highlights the exact range to be tested. Problem is, the return is always 1
Really liked the loop, would hate to have to hard code this

 ObjXL.Range("A6").Select
    ObjXL.ActiveCell.Offset(0, 8).Range("A1").Select
       For ColumnCounter = 1 To 15
         ObjXL.ActiveCell.Offset(0, 1).Range("A1").Select
                If ObjXL.Application.WorksheetFunction.CountA(ObjXL.ActiveCell.Range("A1:A" & rsRowCount).Select) = 0 Then
                       ' function "Hide this column!"  ' Checks Start row, column - range is dependent on Recordset
                       Debug.Print ObjXL.Application.WorksheetFunction.CountA(ObjXL.ActiveCell.Range("A1:A" & rsRowCount).Select)
                Else
                    ' do nothing
                 End If
       Next