Excel VBA: Count Columns with Data (2 Examples)

Get FREE Advanced Excel Exercises with Solutions!

When you have a small data table with a few columns, you can easily count them all. But for a large data table, it’s quite difficult to count all the columns without any error. Besides that, some columns may contain data, whereas some columns may be completely blank. Thus counting all the columns with data in case of a large data table is quite hard. In this article, you will learn 2 ways to count columns with data using VBA in Excel with ease.


Excel VBA: Count Columns with Data: 2 Ways

1. Count All the Columns in a Worksheet with Data Using a Simple VBA Code

We can see the 3 columns in the following data table. Now we will use a VBA code to determine the number of used columns in a single Excel worksheet.

❶ First of all, press ALT + F11 to open the VBA editor.

❷ After that create a new module from Insert > Module.

Insert a New Module: Count All the Columns in a Worksheet with Data Using VBA in Excel

❸ After that copy the following VBA script.

Public Sub CountUsedColumns()
With Sheet1.UsedRange
MsgBox "The number of columns with Data is: " & .Columns.Count
End With
End Sub

❹ Then go back to the VBA editor and press CTRL + V to paste it.

❺ Now save the VBA code by pressing CTRL + S button.

Count All the Columns in a Worksheet with Data Using VBA in Excel

❻ To run the VBA code,  go to the Developer tab and click on Macros. Or, simply press the F5 key.

The Macro dialog box will appear.

❼ All you need to do is, click on the Run button.

Macro dialog box : Count All the Columns in a Worksheet with Data Using VBA in Excel

Now you will see that a dialog box has appeared. Which says, the number of columns with data is 3.


2. Count All the Columns in a Given Range with Data Using an Excel VBA Code

The following VBA code will enable you to count all the columns with data in a given range.

To use the code,

❶ First of all, open the VBA editor by pressing ALT + F11.

❷ Then go to Insert > Module to create a new module.

Insert a new module: Count All the Columns in a Given Range with Data Using VBA in Excel

❸ After that copy the following VBA script.

Sub CountColumnsInARange()
Dim xRng As Worksheet
Set xRng = Worksheets("Sheet1")
MsgBox "Total column: " & xRng.Range("B5:D5").Columns.Count
End Sub

❹ Go back to the VBA editor and press CTRL + V to paste it.

❺ Now save the code by pressing CTRL + S.

Count All the Columns in a Given Range with Data Using an Excel VBA Code

❻ Go to the Developer tab and hit the Macros to open the Macro window.

Or, simply just press the F5 key to avail the Macro dialog box.

❼ From the dialog box, choose the function name CountColumnsInARange and hit the Run button.

Finally, a small dialog box will appear that says the total number of columns is 3.


More VBA Codes to Count Columns in Excel

1. Use VBA Range.End Method to Return the Last Used Column Number

To get the last used column number in an Excel spreadsheet,

❶ First of all, open the VBA editor by pressing ALT + F11.

❷ Then go to Insert > Module to create a new module.

❸ Copy the following VBA code:

Option Explicit
Sub LastColumn()
Dim xRng As Integer
xRng = Range("B4").End(xlToRight).Column
MsgBox xRng
End Sub

❹ Paste and save the code in the VBA editor.

Use VBA Range.End Method to Return the Last Used Column Number

❺ Press the F5 key to run the above code.

You will get the last column number in a pop-up dialog box as in the picture below:


2. Use Range.Find Method to Return the Last Used Column Number in Excel

If you are looking for VBA codes to return the last used column number then follow the steps below:

❶ First of all, press ALT + F11 to open the VBA editor.

❷ After that create a new module from Insert > Module.

❸ Copy the following VBA code:

Sub LastUsedColumnNo()
Dim xRng As Long
    xRng = Cells.Find(What:="*", _
                    After:=Range("B4"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Column
    MsgBox "Last Used Column Numeber: " & xRng
End Sub

❹ Then go back to the VBA editor and press CTRL + V to paste it.

❺ Now save the VBA code by pressing CTRL + S button.

Now, press the F5 key to run the above code.

You will get the last used column number in a pop-up dialog box as in the picture below:


Things to Remember

  • Press ALT + F11 to open the VBA editor.
  • You can press ALT + F8 to avail the Macros dialog box.
  • To run the VBA code, press the F5.

Download Practice Workbook


Conclusion

To sum up, we have discussed 2 ways to count columns with data using VBA in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP. Please visit our website ExcelDemy to explore more.


<< Go Back to Count Columns | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo