Last Row of currentRegion - How

Posted on 2012-08-28
Last Modified: 2012-08-28
Hello All,

Get the last Row of current Region
I have several current regions in my sheet.

What I need: I need the last row number of the current region…..
Question by:Rayne
    LVL 10

    Accepted Solution

    Assuming that "sheet1" is a variable you have defined then the code below will work, but you might have meant Sheets("Sheet1") instead; if so, make that substitution.
    Dim rgn As Range
        Set rgn = sheet1.Range(“BB2”).CurrentRegion
        lastRow = rgn.Row + rgn.Rows.Count - 1

    Open in new window

    LVL 8

    Assisted Solution

    by:Elton Pascua
    LVL 17

    Assisted Solution

    techfanatic's answer will only work if the current region starts in row 1. Not for points, but I usually use a variant of tdlewis:
    Dim rgn As Range
        Set rgn = Sheet1.Range("BB2").CurrentRegion
        lastrow = rgn.Rows(rgn.Rows.Count).Row

    Open in new window


    Author Comment

    Thank you all :)

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now