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.
50+ Project Management Templates Pack
Excel PowerPoint Word
Advanced Project Plan & Portfolio Template
Business Presentations Templates Pack
20+ Excel Project Management Pack
20+ PowerPoint Project Management Pack
10+ MS Word Project Management Pack
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.
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
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Insert a New Module from Insert Menu
- Copy the above code and Paste in the code window
- 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
- Now Run a Macro, you should see a message box as shown below
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.
- User wants to paste his data for 5 Departments
- 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)
- Find maxim sales from 5 departments and Bold the Font where ever it is maximum
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
Download the file below and Click on the FindMax button and Explore the Code.
Download the example file and Explore it.