Excel count columns in a range

I need an efficient and preferably non-volatile worksheet function to count the columns in a supplied range.  I am using e.g.
=COUNTA($A$1:$D$1)+COUNTBLANK($A$1:$D$1)

Open in new window

for a range that has only one row, or I guess I could have
=COUNTA(INDEX($A$1:$E$22,1,))+COUNTBLANK(INDEX($A$1:$E$22,1,))

Open in new window

for a range with multiple rows.

Obviously I could have a simple UDF like
Public Function ColCount(ByRef rngIn As Excel.Range) As Long
    ColCount = rngIn.Columns.Count
End Function

Open in new window

to do this, but I'm looking for a worksheet function if possible.

Any better ideas than mine?
LVL 17
andrewssd3Asked:
Who is Participating?
 
barry houdiniCommented:
Try using COLUMNS function

=COLUMNS(range)

regards, barry
0
 
NorieVBA ExpertCommented:
Do you mean the no of columns with data?

If you don't then you can just use COLUMNS which will return the no of columns in a range.
0
 
barry houdiniCommented:
Thanks for the grade....

Just a note on that first formula you listed

=COUNTA($A$1:$D$1)+COUNTBLANK($A$1:$D$1)

You have to be careful with that if any of the cells contains "formula blanks" (null strings) as both COUNTA and COUNTBLANK count those....so it's possible that that formula could give you a result of 4,5,6,7 or 8!

regards, barry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.