## 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
If Err.Number > 0 Then
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,…

• 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.