0

Is there a better way to accurately count the rows of a table without running a whole page of codes?

I am currently using sheet1.usedrange.rows.count but that counts even the blank rows or rows that were previously used but contents were cleared later on.

I have used LastRow = Sheet1.Cells(Sheet1.Cells.Rows.Count, "M").End(xlUp).Row before but I can't use this now as column M or any of my other columns can have blank cells in them.

I just needed to easily delete rows that do not have any cells with contents.

Sarah

Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
user3682866
  • 89
  • 2
  • 4
  • 8
  • 1
    possible duplicate of [Error Finding Last Used cell In VBA](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) (look into _"Find Last Row in a Sheet"_ section) – Dmitry Pavliv Jun 01 '14 at 09:14

1 Answers1

-1

You can iterate through each column of your table, for example, like this:

Sub LastUsedRowInRange()

Dim sh As Worksheet
Dim dLastRow As Double
Dim dCurrRow As Double
Dim sRange As String
Dim rn As Range
Dim i As Integer
Dim iFirstCol As Integer
Dim iLastCol As Integer

Set sh = Sheets("Sheet1")
sRange = "A1:P30"
Set rn = sh.Range(sRange)
iFirstCol = rn.Column
iLastCol = rn.Columns.Count + rn.Column - 1

For i = iFirstCol To iLastCol
    With sh
        dCurrRow = .Range(Cells(.Rows.Count, i), Cells(.Rows.Count, i)).End(xlUp).Row
        dLastRow = IIf(dCurrRow > dLastRow, dCurrRow, dLastRow)
    End With
Next

Debug.Print dLastRow

End Sub
Dmitry
  • 421
  • 4
  • 14
  • Wow this is too long :s I followed the link simoco posted above, the answer from Sid is good, I also left my own version there. This post may need to be closed. – user3682866 Jun 01 '14 at 11:48
  • Well, yes, it is long but it is a full working example. Besides that it gives the last row for a range i.e. if the first column of the range has only 10 rows filled in while the second all 30 it still gives the correct answer while the example you refer to neither full working nor for a range. – Dmitry Sep 19 '14 at 18:43