Finding last used Row with data in particular Column – Excel VBA Examples

Home/Excel VBA/Finding last used Row with data in particular Column – Excel VBA Examples

VBA Find Last Used Row with data in particular Column – Excel Macros Examples Codes helps in automation. There are certain situations where we perform some tasks by finding last used Row with data in a Column. For examples, There may be many columns with data and each column may have different number of items (rows). In this situation we need to find exact number of rows in a specific column to avoid the unnecessary looping of all rows even if there is no 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 count the number of rows in the active sheet from there we can come back in particular row to get the exact number of rows with data. We can use Row property to get last used Row.

Finding last used Row with data in particular Column – Example

The following example will show you how to find last Row with data in a particular Column. In this example we are finding the last used Row in Column A.

Sub sbLastRowOfAColumn()
'Find the last Row with data in a Column
'In this example we are finding the last row of column A
Dim lastRow As Long
With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
MsgBox 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 first Column at A15
  6. Now Run a Macro, you should see a message box with result 15
Output:

Here is the example data and screen-shot with output to show how Finding last used Row with data in particular Column works.

find last row with data in a Column excel vba - Example

Finding last used Row with data in particular Column- Case Study

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

Requirement:
  1. User wants to paste his data for 5 Departments – one department data in one row
  2. Number of items in each Column could be any thing between 1 to 16(it is not fixed in all the Columns)
  3. Data may not be available for all Departments
  4. Find maxim sales from 5 departments and Bold the Font/li>
Code:
Sub sbLastRowOfAColumnExamples()
Dim lastRow, lRow As Integer
Dim iCntr, jCntr, iMaxRow As Integer
Dim vMax
For iCntr = 1 To 5 ' for each column
vMax = 0
iMaxRow = 2
'Finding last Row of current Column
With ActiveSheet
lastRow = .Cells(.Rows.Count, iCntr).End(xlUp).Row
End With
lRow = lastRow
Range(Cells(2, iCntr), Cells(lRow, iCntr)).Font.Bold = False
For jCntr = 2 To lRow
If vMax < Cells(jCntr, iCntr) Then
vMax = Cells(jCntr, iCntr)
iMaxRow = jCntr
End If
Next
Cells(iMaxRow, iCntr).Font.Bold = True
Next
End Sub
Instructions:

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

Output

find last row with data in a Column excel vba - Case study

Download Example File

Download the example file and Explore the use of Finding last used Row with data in particular Column.
Download Now :mongopono.ru -Last Row with Data in a Column

LIMITED TIME OFFER
By |March 31st, 2013|Excel VBA|6 Comments

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.

6 Comments

  1. Ram September 25, 2014 at 11:24 PM - Reply

    Range(“A:A”).SpecialCells(xlCellTypeLastCell).Select
    ‘to select last cell from column “A”, same code can be used to find last cell from row.

    Feedback! Waiting…..

    • PNRao September 26, 2014 at 9:52 PM - Reply

      Hi Ram,
      This works fine if your data increases always.
      Try this case:
      -> Add a new worksheet.
      -> Enter some value at Range A10 and A15.
      ===========> You code give the correct result as 15.
      -> Now delete data at A15
      ->Now run you code, again it will return 15, instead of 10.
      So, you code always return last used row number (even if no data).
      ————
      Example show in this topic will return the correct, last row number with data.

      Hope this clarifies your query.

      Thanks-PNRao!

  2. sai babu June 3, 2015 at 1:08 AM - Reply

    Happy morning Mr. PN Rao,

    If I’m not wrong, the following code will also gives the above same results, without predefine column number. .

    Let me know, are all of your course is available free of cost or for any payment

    Any better ideas or modifications are highly appreciable.

    sai babu

    Sub to_find_max_value ()
    '
    Dim Lrow As Integer, Lcol As Integer, imx As Integer, ist As Integer
    '
    'Finding last Row & Column in the table
    With ActiveSheet
    Lrow = .Cells(.Rows.Count, 1).End(xlUp).Row ' Last row
    Lcol = .Cells(1, .Columns.Count).End(xlToLeft).Column ' Last column
    '
    For ist = 1 To Lcol
    '
    imx = Application.WorksheetFunction.Max(Range(Cells(ist, ist), Cells(Lrow, ist)))
    '
    ' To find the max. value in range
    .Range(Cells(1, ist), Cells(Lrow, ist)).Find(What:=imx, _
    After:=Cells(ist, ist), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    '
    ' Font bold and background colour to selected / Maximum value
    ActiveCell.Font.ColorIndex = 3
    ActiveCell.Interior.ColorIndex = 6
    '
    Next
    '
    End With
    '
    End Sub
    
    • PNRao June 3, 2015 at 12:28 PM - Reply

      Good morning Mr. Sai!
      Yes, there are many ways to solve the same problems. The code which we are providing here to help both beginners and Advanced users.
      And regarding the courses, there are lot of topics available in our blog for a free reference for VBA developers. The online courses which we are going to start will cost 60 to 100 USD (3500/- to 6000/-). We will send you the exact pricing in couple of weeks.

      Thanks-PNRao!

  3. Rico July 15, 2015 at 2:55 PM - Reply

    How can we do it if we are searching from a table (listobject)? When I tried the code, it always goes to the bottom of the table that is not part of the table. Example, I defined the table as A1:B10. It has data until B5 only. So the unused cell in column B should be B6 – but it goes to end of table. Could you please help? 🙂

  4. Salim May 30, 2017 at 3:55 PM - Reply

    try this code

    Option Explicit
    Sub find_last_row()
    Dim My_Col, My_Ro, i As Long
    Dim My_Max As Byte

    My_Max = 0
    My_Col = ActiveSheet.Cells(1, Columns.Count).End(1).Column

    For i = 1 To My_Col
    My_Ro = Cells(Rows.Count, i).End(3).Row
    If My_Max < My_Ro Then My_Max = My_Ro
    Next

    MsgBox "The laste used row is:" & My_Max
    End Sub

Leave A Comment


Related pages


dbms commands with examples pdfhow to use sum if formulavlookup limitationsvba code libraryworking with arrays in excel vbaapplying filters in excelexcel vba rangesexcel vba current rowmacro to close workbookactivecell interior colorindexmacro recorder excel 2010excel formatting cellsunlock vbaproject milestone templatevba scripting filesystemobjectconditional statement in excelapplication screenupdating trueproject management tracker excelmerge columns excelpassword protect an excel workbookvba substitutetimeline template in excelcombining if statements in excelexcel vba removeexcel macro database queryexcel mark duplicatescombine data from multiple sheets in exceldcl commandconditional statements worksheetvba code for msgboxddl in dbmshow to create a clustered column chart in excelexcel vba userformsql aptitude questions with answersexcel macro valueexcel how to unhide allvba formula in cellmultiple if conditions in excelhow to unhide multiple columns in excelexcel range lookupmacro learning in excelvbscript code examplesrunning macro in excelcool excel spreadsheetsexcel simple macroimport text file to excel vbahyperlink formula excelexcel events vbadifference between two dates in excelexcel vba file pathcopy worksheet to another workbookfind duplicates in a column in excelexcel macro combo boxexcel vba assign range to arrayproject reporting template excelcreating a bubble chart in excel 2010vb exit buttondelete blanksoffset excel functionloop function in excelexcel userform tutorialvba rgb color codesconditional macrosvba insert multiple rowsdata analysis add in excel 2007how to insert tick mark in excel 2007how to insert checkbox in excel 2007vba excel pivot tablehow to protect excel sheet 2007pricing model template in excelvlookup multiple matcheshighlight duplicate rows in excel 2010duplicate cells exceldefinition of merge in excelexcel unprotect macrovbinformation vba