Description:

We need to find last Column with data in a Worksheet Sheet if we want to loop through all columns of the worksheet using Excel VBA. For examples, my data changes every time but I need to perform same task for each column. In this situation I need to know the last column of the data every time the perform the task.

Solution(s):

We can use .Cells.SpecialCells(xlLastCell).Column property to get last used Column then we can loop through the Columns from back to check if any data exist..

Find Last Column with data – Example

The following example will show you how to find last column with data.

Code:
Sub sbLastColumnOfASheet()
'In this example we are finding the last Column with data of an Active Worksheet
Dim lastColumn As Long
lastColumn = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
lCol = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
Do While Application.CountA(ActiveSheet.Columns(lCol)) = 0 And lCol <> 1
lCol = lCol - 1
Loop
lastColumn = lCol
MsgBox lastColumn
End Sub
Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a New Module from Insert Menu
  4. Copy the above code and Paste in the code window
  5. To check, enter some data in a worksheet (suppose at E5) and delete and enter some data before this column (suppose at C4). So, your last used column is E and Column with Data is C, so the result should be 3
  6. Now Run a Macro, you should see a message box as shown below
Output

find last column with data using excel vba

Find Last Column with data – Case Study

The following example will show you real time example and when need find the last column with data.

Requirement:
  1. User wants to paste his data for 5 Departments
  2. Number of regions could be any thing between 1 to 15 (In this data it is 12, but it is not fixed- it will change)
  3. Find maxim sales from 5 departments and Bold the Font where ever it is maximum
Code:
Sub sbLastColumnOfASheetFindMax()
Dim lastColumn, lCol As Integer
Dim iCntr, jCntr, iMaxCol As Integer
Dim vMax
lastColumn = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
lCol = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
Do While Application.CountA(ActiveSheet.Columns(lCol)) = 0 And lCol <> 1
lCol = lCol - 1
Loop
lastColumn = lCol
Range(Cells(2, 2), Cells(6, lCol)).Font.Bold = False
For iCntr = 1 To 5
vMax = 0
iMaxCol = 2
For jCntr = 1 To lCol
If vMax < Cells(iCntr + 1, jCntr + 1) Then
vMax = Cells(iCntr + 1, jCntr + 1)
iMaxCol = jCntr + 1
End If
Next
Cells(iCntr + 1, iMaxCol).Font.Bold = True
Next
End Sub
Instructions:

Download the file below and Click on the FindMax button and Explore the Code.

Output

find last column with data using excel vba- example case

Example File

Download the example file and Explore it.

ANALYSISTABS -Last Column with Data

Hey! Join Our Community

Get Quick Responses & Experts' Answers in Minutes!
Get Notified - When Answered Your Question!