Find Last Column with data in Worksheet using Excel VBA

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
• Find Last Column with data – Case Study
• Download: Find Last Column with data – Example File

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 – 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

Example File

Download the example file and Explore it.

ANALYSISTABS -Last Column with Data