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.

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 :ANALYSISTABS -Last Row with Data in a Column

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 SALE80% 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: March 31st, 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.