How to count rows in a table with a user defined column

[Graebeard]

Hi folks,

I have a dynamic table to track activities. There are a bout a dozen fields for each record and not all are always filled in. One column titled "Activity" is always filled in, BUT, since we may add or delete columns, the Activity field is not always in Column G.

I'm writing a VBA macro for the Sort, so I need to know which column to use to count the number of records to be sorted. The field names and their columns are stored in an array called myTitles(x,1) and myTitles(x,2).

I was trying something like noRecords = Counta((myTitles(x,2):myTitles(x,2)) which is similar to the cell formula CountA(G:G)

Obviously it did not work. Any help would be appreciated

TIA

Graebeard

 

[JoeMo]

Counta is a worksheet function that requires range arguments. Why not use the Range.Find method to find "Activity" then use the found ranges column number like so:

Dim R as range, x as Long
set R = cells.Find("Activity")
if R is Nothing then Exit sub
x = R.Column
noRecords = Application.Counta(Columns(x))
'rest of code

 

[Graebeard]

Counta is a worksheet function that requires range arguments. Why not use the Range.Find method to find "Activity" then use the found ranges column number like so:

Dim R as range, x as Long
set R = cells.Find("Activity")
if R is Nothing then Exit sub
x = R.Column
noRecords = Application.Counta(Columns(x))
'rest of code

Thanks for the quick reply JoeMo.
I've been using Excel since version 3 (I think that was around the mid '80s or so), and writing VBA macros for the past 25 years or so, but I'm always finding something new. The cells.find, the r.column and application.counta(columns(x)) are completely new to me.

So, many thanks. With a little tweaking, it works great.

Graebeard