Finding Last Row with data (Excel 97/SR2)

Topic

New Reply

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??

    Dim R As Range
    Dim cell As Range
    Dim rowNum As Integer
    Set R = Range("D1:D2000")  'would never be more than 2000 rows
    For Each cell In R
        If IsEmpty(cell) Then
            rowNum = ????????????
        End If
    Next

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

Reply | Quote

Replies

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.

Dim rng As Range, lngLastRow As Long
Set rng = ActiveCell.CurrentRegion
lngLastRow = rng.Rows.Count

Hope the above is of use to you.

Andrew C

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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 Q163263 for more information. –Sam

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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. –Sam

Reply | Quote

Legare,

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

A space is data and should be treated as such.

Reply | Quote

This code will find the last row in column A:

Dim iLastRow As Integer
    iLastRow = Range("A65536").End(xlUp).Row

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

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

Reply | Quote