Loop - same value using Application.WorksheetFunction.CountA(ActiveCell.Range("A1:A" & rsRowCount).Select)

[Tesla]

Using remote Automation from MS Access, the ObjXL is just an object reference.
A recordset (using copy from recordset) inserts data at cell A6. The Recordcount is known.
The recordcount variable is used to set the range to match the number of records returned.
There are 15 columns midway - if an entire column is Empty - then it should be hidden.

This code worked great for a hard-coded range!!

? objxl.Application.WorksheetFunction.CountA(objxl.Range("L6:L20"))

Tried to apply it using the offset and with the ActiveCell. Stepping through debugger, it perfectly highlights the perfect range in each step. PROBLEM - it always returns 1 no matter if empty or populated for each range in the loop.

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

 

[Norie]

You should avoid using ActiveCell/Select, especially when automating Excel from another application.

Why not try setting and using a range variable?

I'd post some code for that but I don't know what ObjXL actually refers to, is it an instance of Excel, an Excel workbook, an Excel worksheet...

 

[Russell Hauf]

Not sure why you are going to A6 then to I6 from that, so I just went directly to I6. If you need to back into it, change that piece of code. But I think this should at least get you on your way...one way to do it:

<font face=Courier New>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rng = objXL.ActiveSheet.Range("I6")<br>    <br>    <SPAN style="color:#00007F">For</SPAN> ColumnCounter = 1 <SPAN style="color:#00007F">To</SPAN> 15<br>        <SPAN style="color:#00007F">If</SPAN> objXL.WorksheetFunction.CountA(rng.Resize(rsRowCount)) = 0 <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#007F00">' Hide current column</SPAN><br>            rng.EntireColumn.Hidden = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = rng.Offset(0, 1)<br>    <SPAN style="color:#00007F">Next</SPAN> ColumnCounter<br></FONT>

Edit: Oops - changed Application to objXL

 

[Tesla]

Sorry for the delay to respond. The code above is for a new set under development. Pushed out some major automated Excel today for executive level reviews.
The ObjXL is just an Excel Application. Sorry to not have mentioned that.
An instance of Excel is opened by cade in MSAccess with Linked Tables to SQL Server. The SQL Server uses Table Valued Functions and substantial code to create the records.
The ObjXL then opens a specific instance of an Excel workbook that contains the report title and column headers. The workbook is custom named and Saved As into a specific delivery folder.
A basket of custom Excel macros are run from MS Access on the workbook. It can vary from pivot, grouping / sorting, and many other things.

No problem, adding ObjXL at the proper place is easy to do.
Using the Range object looks GREAT!
Will give it a try and report back. THANKS!
The code posted, when using the VBA editor and watching the Excel with Objxl.visible = true - looked as if it was selecting the range.
But, there is nothing like actually using the Range object.

Thanks to both of you for your help.