check for data in last row

Posted on 2014-08-23
Last Modified: 2014-08-24
The following line checks for the last row of data by looking in column "A" only:

lastrow = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row

My worksheet has 5 columns of data (columns A through E).  Not every cell will always contains data.  Therefore, on the last row, there could be times where, for instance, column B, C, D and E contain data but A does not.  I need to check all columns in the used range, not just column A, to determine the last row.  How do I modify the above code to do that?
Question by:dbfromnewjersey
    LVL 76

    Expert Comment

    There is a UsedRange property, so try:
    lastrow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 

    Open in new window

    LVL 76

    Accepted Solution

    I seem to have placed the '/code' markup in the wrong place. Here is a correction

    There is a UsedRange property, so try:
    lastrow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count -  1  

    Open in new window


    Author Comment

    Thank you.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    732 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

    24 Experts available now in Live!

    Get 1:1 Help Now