Excel count columns in a range

Posted on 2011-10-07
Last Modified: 2012-05-12
I need an efficient and preferably non-volatile worksheet function to count the columns in a supplied range.  I am using e.g.

Open in new window

for a range that has only one row, or I guess I could have

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?
Question by:andrewssd3
    LVL 50

    Accepted Solution

    Try using COLUMNS function


    regards, barry
    LVL 33

    Expert Comment

    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.
    LVL 50

    Expert Comment

    by:barry houdini
    Thanks for the grade....

    Just a note on that first formula you listed


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

    regards, barry

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    This collection of functions covers all the normal rounding methods of just about any numeric value.
    Article by: Leon
    Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now