Excel VBA: Count Columns with Data (2 Examples)

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.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


2 Ways to Count Columns with Data Using VBA in Excel

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.

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. And please visit our website Exceldemy to explore more.

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo