Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

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?
0
andrewssd3
Asked:
andrewssd3
  • 2
1 Solution
 
barry houdiniCommented:
Try using COLUMNS function

=COLUMNS(range)

regards, barry
0
 
NorieData ProcessorCommented:
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now