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