VBA - Range.Row.Count

I have written a simple code to illustrate my predicament.

Sub test()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")

    Dim k As Long

    k = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count
End Sub

What happens is this: We count the rows which contain values starting at A1. If the number of rows which contain values is > 1 the code works great. However, if A1 is the only cell which contains any value, k = 1,048,576 which I guess is the maximum number of rows allowed in Excel.

Why doesn't k = 1?

Pictures:

EDIT: The workaround that I'm using is the following:

Sub test()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")

    Dim k As Long

    k = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count
    If k = 1048576 Then
        k = 1
    End If
    MsgBox (k)
End Sub

Since k is always equal to 1048576 when the number of rows with values is 1. It just feels a bit silly having to do something like this.

Probably a better solution is work upwards from the bottom:

k=sh.Range("A1048576").end(xlUp).row

VBA - Range.Row.Count, CurrentRegion.rows.count Why not use an Excel formula to determine the rows ? This can be used for finding filled cells in a row too. I had been trying a combination of excel and vba and had got this to work - but its� Count number of rows in a range using VBA. Output Range: Select the output range by changing the cell reference ("B5") in the VBA code. Range: Select the range from which you want to count the number of rows by changing the range reference ("E5:E15") in the VBA code.

You should use UsedRange instead like so:

Sub test()
    Dim sh As Worksheet
    Dim rn As Range
    Set sh = ThisWorkbook.Sheets("Sheet1")

    Dim k As Long

    Set rn = sh.UsedRange
    k = rn.Rows.Count + rn.Row - 1
End Sub

The + rn.Row - 1 part is because the UsedRange only starts at the first row and column used, so if you have something in row 3 to 10, but rows 1 and 2 is empty, rn.Rows.Count would be 8

Determine the number of rows in a range, Rows.Count returns 2, not 4. To use this property on a range that may Have questions or feedback about Office VBA or this documentation? Open VBA Editor by using Alt + F11 and enter the following code. Sub CountRows1() Dim last_row As Long last_row = Cells(Rows.Count, 1).End(xlUp).Row MsgBox (last_row) End Sub. Code explanation

CountRows = ThisWorkbook.Worksheets(1).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count

Range.Rows property (Excel), UsedRange – Find Last Used Cell, Column or Row. The following code will return a message box indicating the total number of rows used in a worksheet. I need to count the number of visible rows in filtered range (ie those that meet all criteria) First I tried autofilter.rows.count but that counts all the row so I wrote the sub below but it can be very slow Dos anyone have a better way to do this thanks in advance Sub

You could also use the "Last" function from Ron de Bruin (http://www.rondebruin.nl/win/s9/win005.htm), it worked perfectly for me and also gives back the last column and cell if you want. To get the last row use it like

lastRow = Last(1,yourRange)

I found this quite handy.

Function Last(choice As Long, rng As Range)
'Ron de Bruin, 5 May 2008
' 1 = last row
' 2 = last column
' 3 = last cell
    Dim lrw As Long
    Dim lcol As Long

    Select Case choice

    Case 1:
        On Error Resume Next
        Last = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row
        On Error GoTo 0

    Case 2:
        On Error Resume Next
        Last = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

    Case 3:
        On Error Resume Next
        lrw = rng.Find(What:="*", _
                       After:=rng.Cells(1), _
                       Lookat:=xlPart, _
                       LookIn:=xlFormulas, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious, _
                       MatchCase:=False).Row
        On Error GoTo 0

        On Error Resume Next
        lcol = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

        On Error Resume Next
        Last = rng.Parent.Cells(lrw, lcol).Address(False, False)
        If Err.Number > 0 Then
            Last = rng.Cells(1).Address(False, False)
            Err.Clear
        End If
        On Error GoTo 0

    End Select
End Function

VBA Used Range, Count Rows in a Selection; Count Columns in a Selection; VBA Coding Made Easy to count the number of rows that were selected, use Selection.Rows. Count "At Automate Excel we offer a range of free resources, software, training, and� The result is 1,048,576 – the number of rows inside a worksheet. If you want to quickly find the number of rows inside a worksheet, press Ctrl + Down Arrow . The cursor will be instantly moved to the last row. In a similar way, you can get the number of rows inside a range.

In case anyone looks at this again, you can use this:

Sub test()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")

    Dim k As Long
    k = sh.Range("A1", sh.Range("A1").End(xlDown).End(xlDown).End(xlUp)).Rows.Count
End Sub

Count the rows in a Selection - VBA Code Examples, Hi all, Just looking for some help. I'm looking to count the number of rows starting in a specfic cell and to the bottom of the range. The start of the� Range.Count property (Excel) 05/10/2019; 2 minutes to read +1; In this article. Returns a Long value that represents the number of objects in the collection.. Syntax. expression.Count

VBA, I would like to share on how to count the number of rows and colums in a range using VBA. Hope it can help. (Code, 31 lines) In this ArticleUsedRange – Find Last Used Cell, Column or RowFind First Empty CellCount Used Columns In WorksheetLast Used Cell – Problems UsedRange – Find Last Used Cell, Column or Row The following code will return a message box indicating the total number of rows used in a worksheet. Empty rows are considered used if…

Count Number Of Rows & Columns in a Range, Find the Last Row, Column, or Cell in Excel VBA with the Range. last non- blank cell in Duration: 6:56 Posted: May 11, 2015 When applied to a Range object that is a multiple selection, this property returns rows from only the first area of the range. For example, if the Range object someRange has two areas—A1:B2 and C3:D4—, someRange.Rows.Count returns 2, not 4.

VBA Tutorial: Find the Last Row, Column, or Cell in Excel, Count number of rows in a range. This tutorial shows how to count the total number of rows from a selected range through the use of an Excel formula or VBA. In this ArticleCount Rows in a Selection Count Columns in a SelectionVBA Coding Made Easy If you ever need to count the number of rows that were selected, use Selection.Rows.Count Count Rows in a Selection Public Sub CountMyRows() MsgBox Selection.Rows.Count End Sub If you ever need to count the number of Columns that were selected,…

Comments
  • If I use this, it will looped around 5000 times. Will this slow down the code significantly? because counting from 1 million, 5000 times seems a bit inefficient.
  • Excel stores information sparsely, so it does not need to look at the intermediate cells. So no, it won't slow down - its very fast
  • After testing all the solutions this was the quickest one. Thanks!
  • Sheets can have a different number of possible rows depending on the version of Excel. You should use Rows.Count instead of that large static number in your range object.
  • Why it return Watch 1048569 when I delete some row in table?
  • I can't use this solution because I'm counting the rows in multiple columns. Example: i.imgur.com/cdCx0Or.png