Mrinmoy Roy
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.
❸ 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.
❻ 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.
Now you will see that a dialog box has appeared. Which says, the number of columns with data is 3.
Read More: How to Count Number of Columns in Excel (3 Easy Methods)
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.
❸ 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.
❻ 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.
Read More: How to Count Columns for VLOOKUP in Excel (2 Methods)
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.
❺ 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:
Read More: How to Count Columns until Value Reached in Excel
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.
