CODEWITHSUNDEEP
vbaexcel
Mateen Ulhaq
Mateen Ulhaq

Reputation: 27271

Total number of rows

How do I determine the total number of used rows in my worksheet?

Dim rows As Integer
rows = ActiveWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0).Rows.Count

I need to know so I can loop through all my rows:

For row = 1 To rows
    ' Check if row contains pattern.
    match = ReFind(Range(row & ":" & row), "^.*(\[KA\])|(\[KIC\])|(\[KS\])|(\[NA\]).*$")

    If match <> "" Then
        Call CopyRow(row)
    End If
Next row

Upvotes: 0

Views: 2501

Answers (1)

chris neilsen
chris neilsen

Reputation: 53166

Using .Cells(.Rows.Count, "A").End(xlUp).Row will return the last row used in a particular column, not necessarily the last row with any cell used.

Another way is to use Sheet.UsedRange however you must be aware of certain limitations.

.UsedRange returns a rectangular region bounding all used cells, so it may not be anchoured at cell A1. To get the row number of the bottom of the range use

.UsedRange.Row + .UsedRange.Rows.Count - 1

When deleting rows (or columns) .UsedRange may not update, therefore you may get a range larger than the true used range.

You can force an update: if you are happy to use ActiveSheet then puting the line

Application.ActiveSheet.UsedRange

in your code forces an update.

If you don't want to use ActiveSheet (for example in a routine passed a sheet as a parameter) then use:

i = Sh.UsedRange.Row

Don't rely on the value of i at this point. The next reference to .UsedRange will be the true used range.

Upvotes: 3

Related Questions