VBA - Range.Row.Count

Question

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:

The code works

This is wrong

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.

Charles Williams · Accepted Answer · 2014-07-31 10:23:39Z

38

Probably a better solution is work upwards from the bottom:

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

answered Jul 31, 2014 at 10:23

Charles Williams

23.6k5 gold badges40 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

docjay Over a year ago

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.

Charles Williams Over a year ago

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

docjay Over a year ago

After testing all the solutions this was the quickest one. Thanks!

HackSlash Over a year ago

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.

quangkid Over a year ago

Why it return Watch 1048569 when I delete some row in table?

neelsg · Accepted Answer · 2014-07-31 10:31:24Z

10

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

edited Jul 31, 2014 at 10:31

answered Jul 31, 2014 at 10:24

neelsg

4,8425 gold badges37 silver badges58 bronze badges

1 Comment

docjay Over a year ago

I can't use this solution because I'm counting the rows in multiple columns. Example: i.imgur.com/cdCx0Or.png

Dado · Accepted Answer · 2016-04-20 19:45:15Z

5

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

answered Apr 20, 2016 at 19:45

Dado

1,0262 gold badges12 silver badges21 bronze badges

Comments

suiluj_julius · Accepted Answer · 2016-01-26 17:03:41Z

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

Faysal Ahmed · Accepted Answer · 2018-01-21 08:03:22Z

2

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

edited Jan 21, 2018 at 8:03

Faysal Ahmed

7,6875 gold badges32 silver badges54 bronze badges

answered Jan 21, 2018 at 5:37

greg

211 bronze badge

Comments

lowak · Accepted Answer · 2014-07-31 11:05:23Z

1

That is nice question :)

When you have situation with 1 cell (A1), it is important to identify if second declared cell is not empty (sh.Range("A1").End(xlDown)). If it is true it means your range got out of control :) Look at code below:

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Arkusz1")

Dim k As Long

If IsEmpty(sh.Range("A1").End(xlDown)) = True Then
    k = 1

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

End If

answered Jul 31, 2014 at 11:05

lowak

1,2942 gold badges19 silver badges40 bronze badges

Comments

Paul Roub · Accepted Answer · 2017-12-12 14:51:22Z

1

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

or

k = sh.Range("A2", sh.Range("A1").End(xlDown)).Cells.Count

or

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

edited Dec 12, 2017 at 14:51

Paul Roub

36.5k27 gold badges88 silver badges95 bronze badges

answered Dec 12, 2017 at 14:43

user9089345

111 bronze badge

Comments

APO69 · Accepted Answer · 2019-05-16 12:32:33Z

1

The best solution is to use

Cells(Rows.Count, 1).End(xlUp).Row

since it counts the number of cells until it finds the last one written.

Unlike

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

what it does is select an "from-to" range and display the row number of the last one busy.
A range implies two minimum values, so ... meanwhile A1 has a value of the range continues to count to the limit (1048576) then it is shown.

Sub test()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets(1)
Dim k As Long

    k = Cells(Rows.Count, 1).End(xlUp).Row
    MsgBox k

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

End Sub

edited May 16, 2019 at 12:32

answered May 16, 2019 at 9:26

APO69

1115 bronze badges

Comments

user3205578 · Accepted Answer · 2014-07-31 10:47:47Z

0

Have you tried :-

Sub test()

    k = Cells(Rows.Count, "A").End(xlUp).Row

    MsgBox (k)

End Sub

The /only/ catch is that if there is no data it still returns 1.

answered Jul 31, 2014 at 10:47

user3205578

Comments

Vladimir Vagaytsev · Accepted Answer · 2016-07-10 21:16:29Z

0

This works for me especially in pivots table filtering when I want the count of cells with data on a filtered column. Reduce k accordingly (k - 1) if you have a header row for filtering:

k = Sheets("Sheet1").Range("$A:$A").SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants).Count

edited Jul 10, 2016 at 21:16

Vladimir Vagaytsev

2,8919 gold badges36 silver badges38 bronze badges

answered Jul 10, 2016 at 15:43

pnacamuli

1

Collectives™ on Stack Overflow

VBA - Range.Row.Count

10 Answers 10

5 Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

10 Answers 10

5 Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related