Finding last used Row with data in Excel Worksheet using VBA

Home/Excel VBA/Finding last used Row with data in Excel Worksheet using VBA

Description:

We need are required to find Last used Row with data if we want to perform certain task on each row of worksheet.If the number of rows are fixed or constant always suppose say 100, we can loop through the 100 rows and do what ever we want to apply. But if the rows are varying, we can loop through the rows to do our tasks by Finding Last Used Row with Data.

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).Row property to get last used Row. And we can loop through the Rows from back to check if any data exist.

Finding Last Used Row with Data – Example

The following example will show you how to find last row with data.

Sub sblastRowOfASheet()
'In this example we are finding the last Row of an ActiveSheet
Dim lastRow As Long
lastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
MsgBox "Last Used Row: " & lastRow
lRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
Do While Application.CountA(ActiveSheet.Rows(lRow)) = 0 And lRow <> 1
lRow = lRow - 1
Loop
lastRow = lRow
MsgBox "Last Row with Data: " & lastRow
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 D10) and delete and enter some data before this Row (suppose at G8). So, your last used row is 10 and Row with Data is 8.
  6. Now Run a Macro, you should see a message box as shown below
Output

find last row with data using excel vba

Finding Last Used Row with Data – Case Study

The following example will show you real time example and when need find the last Row with data.

Requirement:
  1. User wants to paste his data for 5 Regions
  2. Number of Departments could be any thing between 1 to 25 (In this data it is 5 but it is not fixed- it will vary)
  3. Find maxim sales from 5 Regions and Bold the Font
Code:
Sub sblastRowOfASheetFindMax()
Dim lastRow, lRow As Integer
Dim iCntr, jCntr, iMaxRow As Integer
Dim vMax
lastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
lRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
Do While Application.CountA(ActiveSheet.Rows(lRow)) = 0 And lRow <> 1
lRow = lRow - 1
Loop
lastRow = lRow
Range(Cells(2, 2), Cells(lRow, 6)).Font.Bold = False
For iCntr = 1 To 5 'for fixed columns
vMax = 0
iMaxRow = 2
For jCntr = 2 To lRow ' for each row from 2nd row
If vMax < Cells(jCntr, iCntr + 1) Then
vMax = Cells(jCntr, iCntr + 1)
iMaxRow = jCntr
End If
Next
Cells(iMaxRow, iCntr + 1).Font.Bold = True
Next
End Sub
Explanation:

1. ActiveSheet.Cells.SpecialCells(xlLastCell).Row returns the last used Row of the active sheet

2. The below code finds the last used Row with data
lRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
Do While Application.CountA(ActiveSheet.Rows(lRow)) = 0 And lRow <> 1
lRow = lRow - 1
Loop
lastRow = lRow 'Last used row with data

3. The first For loop is to loop through the fixed number of Columns (5), i.e Regions
For iCntr = 1 To 5 'for fixed columns
...
Next

4. The second For loop is to loop through the rows (Rows are not fixed), the number of rows we found in step:2
For jCntr = 2 To lRow ' for each row from 2nd row
...
Next

5. In second for loop we are finding the maximum value and changing the font to bold after this loop
vMax = 0
iMaxRow = 2
For jCntr = 2 To lRow ' for each row from 2nd row

If vMax < Cells(jCntr, iCntr + 1) Then vMax = Cells(jCntr, iCntr + 1) iMaxRow = jCntr End If Next Cells(iMaxRow, iCntr + 1).Font.Bold = True

Instructions:

Download the file below and lick on the FindMax button and Explore the Code.

Output

Here is the example screen shot.
find last row with data using excel vba- example case

Finding Last Used Row with Data: Example File

Download the example file and see the example case of finding last used row with data.

mongopono.ru -Last Row 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. ESU December 14, 2015 at 11:14 PM - Reply

    Required All VBA Excel and Access Example

Leave A Comment


Related pages


excel range interior colorcountif syntaxexcel unlock cells without passwordif statement in macro excelsql important interview questionsexcel vba closeproject status report template powerpointexcel pie chart titlefor each in vbahow to change excel columns to rowsexcel formula protectionexcel macro offsetsql dml commands with examplesoffset excel vbaexcel advanced tutorialmultiple criteria in excelscripting.filesystemobject opentextfilevlookup rangevba proceduresvba excel tutorialsunprotect excel sheet onlineexcel weeks between two dateshow to merge multiple cells in excelvlookup easy exampleexcel look up functionsexcel pie chart titleformula match excelsql aptitude questions and answers pdfv look up exampledo while loop in vbaexcel rtrimwhat is adodbvba boxxlupremoving duplicates in excel 2007microsoft excel countifdml dcl ddlcheckbox color changedcl commands in sql with exampleslistbox listindexspotfire basicsexcel 2013 unprotect sheetsql sas tutorialexcel vba write fileexcel formula vlookupclear duplicates in excelascending order excelinsert comment excel shortcutvba scatter plotexcel developer menuexcel vba adodb connection string sql serverexcel rgbwww excel shortcut keysmacros with excelremove duplicate entries from excelvba tutorial accessshortcut key to insert comment in excelcopy vbavba code for msgboxexcel vba insert rowadd developer tab to ribbon in microsoft wordexcel vba connectionto find duplicate records in excelaccess vba run queryexcel macro hide rowsvba writerunlock protected workbookhow do i unlock an excel filesas basics tutorialsvba while loopunprotect password protected excelvlookup falseminutes template excelddl and dmlexcel empty cell testexcel macro waitrgb in vbavba variablesi forgot my excel sheet protection passwordhow to filter out duplicates in excel