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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Ultimate Project Management Template
Excel Template

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


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

LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template

 
Related Resource External VBA Reference