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

Premium Project Management Templates
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE85% OFF
BROWSE ALL TEMPLATES

Advanced Project Planning Templates

Excel Templates

VIEW DETAILS

120+ Project Management Templates Pack

Excel | PowerPoint | Word

VIEW DETAILS

ULTIMATE RESOURCE MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

50+ Essential Project Management Templates

Excel | PowerPoint | Word

VIEW DETAILS

Project Portfolio Management Templates

Excel | PowerPoint Templates

VIEW DETAILS

50+ Excel Project Management Templates

Excel Templates

VIEW DETAILS

By Published On: February 8th, 2013Categories: Excel VBATags:

Share This Story, Choose Your Platform!

About the Author: Valli

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

One Comment

  1. Baskar Rajagopal September 27, 2016 at 8:42 PM

    Hi,

    I’d like to know how to choose the last lines of the sheet. Eg;-, I need to copy the formula of the cells A2:C2 and track the formula to last line.

Leave A Comment