Find Last Column with data in Worksheet using Excel VBA

Home/Excel VBA/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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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

mongopono.ru -Last Column with Data

LIMITED TIME OFFER
By |February 8th, 2013|Excel VBA|1 Comment

About the Author:

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

    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


Related pages


timeline templates for excelunprotect excel sheetscount columns in excelunmerge cells excel 2010excel vba functions tutorialactiveworkbook.savecreating dashboards in excelexcel worksheet copyvba for powerpointvbscript if statement with multiple conditionsvlookup stepssql dcl commandsremoving duplicate entries in exceloffset excel formulaexcel vba select rowfilter in excel vbaremove password from excel workbook 2010unmerge cells excel 2010creating a pie chart in excel 2010excel macro applicationms excel 2007 shortcut keys listmerge two columns in excelunprotect powerpointunprotecting excel workbook without passwordxml converter to excelhow to add developer tab to the ribbonusing the macro recorder record a macromacro insert rowhow to combine excel worksheetsmacro excel loopexcel pivot tables explainedhow to do pivot tables in excel 2013excel vba activex controlsdashboard examples in excelunprotect excel fileselection range vbams access vba functionshow to unprotect excel sheet without passwordexcel vlookup formulashow to record macro in excelvba for excel examplesunprotect excel documentoffset function in excelfiltering duplicates in excelsql interview questions and answers in pdfexcel unprotect sheet password removercombobox additemexcel vb tutorialhow to expand cells in excelhow to put title on excel graphexcel vba fill colorvba excel if statementfor vba loopmacro excel 2007 tutorialvba get file extensionexcel macro to hide rowswhat is dcl in sqlcountif formulaslock an excel filehow to password protect excel 2013excel vba user inputremove duplicates in excel columnhow to use sumifvlookup meaningexcel unprotect worksheetvlookup in microsoft excelexcel hidden sheetexcel vba vlookup examplelearn excel for beginnersvlookup example in hindiproject portfolio management excel templatevlookup formula with if conditionhow to make a dropdown list in excel 2010if iserror formulavba insuranceswot analysis template for powerpointformula for lookup in excelinterview questions & answers for freshers