Finding the number of rows in specified column of data range

sekar_r24 · Apr 4th 2021

Hi.,

How to find the number of rows in specified column of data range. i can able to find the total number of rows from the starting of A1 to the end of the datarange. But my data range starts from b4: d12. It returns 12 and not as 9. how to solve?

Code

lRow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row

**Carim** · Apr 4th 2021

Hello,

Not sure to understand your question ...

Are you simply looking for

Code

Sub Test()
 MsgBox [B4:D12].Rows.Count
End Sub

Hope this will help

**Carim** · Apr 5th 2021

Do not hesitate to post your feedback ... and your comment ...

sekar_r24 · Apr 5th 2021

Thanks Carim.

**Carim** · Apr 5th 2021

Thanks for your Thanks ... AND for the Like

sekar_r24 · Apr 6th 2021

hi.,

But this code works out for a static range of cells. But if the values are changing dynamically, means how to do that.

For Example scenario 1:

the range starts from A1: E5

scenario 2:

C3:F10

scenario 3:

A5:K11

etc.,,,

**royUK** · Apr 6th 2021

Try this

Code

Option Explicit

Function NumberOfRows(cl As Range) As Long
 NumberOfRows = cl.CurrentRegion.Rows.Count
End Function


Sub test()
''/// assumes range starts in C3
MsgBox NumberOfRows(Range("C3"))
End Sub

Display More

**Carim** · Apr 6th 2021

Hello,

You say you are using several different scenarios ...with different ranges ...

BUT all ranges can obviously have their own variables : i for Rows and j for Columns

If you need help for your specific situation, feel free to attach a sample file

Hope this will help

sekar_r24 · Apr 6th 2021

Quote from Carim

BUT all ranges can obviously have their own variables : i for Rows and j for Columns

at particular point of time only on range will be there in the worksheet, which will be anywhere..in the worksheet. as the user will enter in the any cells.

I have to check the no of rows, where the user has entered the data and use for further process with for loop

**Carim** · Apr 6th 2021

To make things a lot clearer ... why don't you attach a sample file ...

sekar_r24 · Apr 6th 2021

hi attached herewith

**Carim** · Apr 6th 2021

Thanks for your sample file ...

But where is your macro ???

**royUK** · Apr 6th 2021

What's wrong with my code?

sekar_r24 · Apr 6th 2021

ROy.,

yours working fine. Thanks.

sekar_r24 · Apr 6th 2021

Carim,

i Have used the code which i had mentioned in the first post of the thread. that calculate the empty row above the starting row too

Code

lRow = Cells.Find(What:="*", _
 After:=Range("C3"), _
 LookAt:=xlPart, _
 LookIn:=xlFormulas, _
 SearchOrder:=xlByRows, _
 SearchDirection:=xlPrevious, _
 MatchCase:=False).Row

**royUK** · Apr 6th 2021

That code finds the last cell with data in.

Try this

Code

Sub x()
    Dim rCl As Range
    Dim lRow As Long
    
    Set rCl = Range("A1")
    lRow = Cells.Find(What:="*", _
    After:=rCl, _
    LookAt:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Row
    
    MsgBox lRow - rCl.Row + 1
End Sub

Display More

My original code is more dynamic though

**royUK** · Apr 6th 2021

Or starting inC3

Code

Sub x()
    Dim rCl As Range
    Dim lRow As Long
    
    Set rCl = Range("C3")
    lRow = Cells.Find(What:="*", _
    After:=rCl, _
    LookAt:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Row
    
    MsgBox lRow - rCl.Row + 1
End Sub

Display More

**royUK** · Apr 6th 2021

Pleased to help.

Post back if you need further help.

Visit my web site, http://www.excel-it.com, for more examples and some helpful articles.

Finding the number of rows in specified column of data range

Participate now!

Share