Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding Last Row with data (Excel 97/SR2)

    I have a spreadsheet with data in columns "A" through "E" and a varying amount of rows. I am looking for some help on how to be able to dynamically determine the last row with data. The purpose for finding the last row of data is to be able to do some sorting and subtotaling. I was able to use a macro and my limited knowledge to get the subtotaling and sorting to work. However I don't know how to determine the last row of data. Listed below is some code I am attempting to make work. The code will check the rows for data but how do I assign the 'row number' to a variable for use later when sorting/subtotaling??
    <pre> Dim R As Range
    Dim cell As Range
    Dim rowNum As Integer
    Set R = Range("D12000") 'would never be more than 2000 rows
    For Each cell In R
    If IsEmpty(cell) Then
    <font color=red>rowNum = ????????????</font color=red>
    End If
    Next</pre>

    The code in red is what I am not sure about. Please help. TIA

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Finding Last Row with data (Excel 97/SR2)

    Steve

    What might finish that for you is rowNum = cell.Row. However, as it is structured even if you only have 10 rows used, it will still return 2000, as it will continue to loop.

    You can however achieve what you want with less code depending on the data in your sheet. If you have just one block of data, then ActiveSheet.UsedRange.Rows.Count will return the number of used rows, and hence the last row number.

    If you have more than one range or block of data you could use the following to return the number of rows in the range that contains the active cell. <pre>Dim rng As Range, lngLastRow As Long
    Set rng = ActiveCell.CurrentRegion
    lngLastRow = rng.Rows.Count</pre>


    Hope the above is of use to you.

    Andrew C

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Last Row with data (Excel 97/SR2)

    This code will find the last row in column A:

    <pre>Dim iLastRow As Integer
    iLastRow = Range("A65536").End(xlUp).Row
    </pre>


    If you need to know the last row in columns A through E that contains data, then this will find it:

    <pre>Dim iLastRow As Integer, I As Integer
    iLastRow = 0
    For I = 0 To 4
    If Range("A65536").Offset(0, I).End(xlUp).Row > iLastRow Then
    iLastRow = Range("A65536").Offset(0, I).End(xlUp).Row
    End If
    Next I
    </pre>

    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Last Row with data (Excel 97/SR2)

    Andrew: I think that will fail if there are any empty rows in the column.
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Last Row with data (Excel 97/SR2)

    Andrew & Legare

    I have quickly tested both methods and they both worked for my needs.

    As a side note, for my purposes, the range of data will not have blank cells. I will only have one range in colums A:E and rows 1:variable. Thanks for the quick response to my problem.

    <img src=/S/salute.gif border=0 alt=salute width=15 height=20> <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding Last Row with data (Excel 97/SR2)

    Andrew's answer is correct, but sometimes Excel get confused as to what has been used or not, so Legare's answer is better, except I don't think xl97 has 65,536 rows, but I only have xl2K here. ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row also works, but who can remember it and it may have the same problem as UsedRange. See <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q163/2/63.asp>Q163263</A> for more information. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Finding Last Row with data (Excel 97/SR2)

    Legare,

    It will work, because it is going from the bottom up
    Greetings,

    Patrick Schouten
    (The Netherlands)

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Last Row with data (Excel 97/SR2)

    On my Excel 97, if I put values in cells A3, A4, and A5 and make A1 the active cell and execute Andrew's code I get lngLastRow=1. If I set A3 as the Active cell, I get lngLastRow=3. If I use my code I get lngLastRow=5. Does it work different on your system?
    Legare Coleman

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Finding Last Row with data (Excel 97/SR2)

    Legare,

    As ActiveSheet.UsedRange.Rows.Count returns the number of used rows in the active sheet (provided that there is no spurious data like formatting or blanks unknown on the sheet), I presume you are referring to the code for the current region. That returns the number of rows in the current region, which if it starts at row 1 is also the last row number. If the range does not start at row 1, then you need an offset added to account or that.

    In the example you gave where A3 was the active cell, the 3 returned was not the row number but the number of rows. (3,4,5).

    With good worksheet management, ActiveSheet.UsedRange.Rows.Count should do the job.

    Andrew

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Last Row with data (Excel 97/SR2)

    You are correct that if there are no empty rows at the top, and no spurious formatting, your formula does return the last row. However, my formula does not depend on any conditions and returns the correct last row number in all cases.
    Legare Coleman

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Finding Last Row with data (Excel 97/SR2)

    Legare,

    As a spurious space is more likely , or at least as likely, there is no ideal solution, as your code also takes space as data.

    I would suggest iLastRow As Long as otherwise any data past row 32,767 would cause a runtime error.

    Andrew

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Last Row with data (Excel 97/SR2)

    You are correct, it should be defined as a Long.

    A space is data and should be treated as such.
    Legare Coleman

  13. #13
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Last Row with data (Excel 97/SR2)

    Sammy, just an FYI but Excel 97 does have 65,536 rows.
    Attached Images Attached Images

  14. #14
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Last Row with data (Excel 97/SR2)

    Excel 95 had ~16,000 rows. Excel 97 & later versions have 65,536 rows. I can't say I've ever come close to that, but apparently I does happen.

  15. #15
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding Last Row with data (Excel 97/SR2)

    When you're as old as me, you forget. Guess I went directly from xl95 to xl2k... I do remember the Out-Of-Memory, OK? messages. Think I'll skip XP also. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •