Count rows

[Hugh]

Hi there,

How to count data rows? I tried following sub. It did not
work.

Private Sub CountRows()
Dim iCount As Long
iCount = 1
While Sheet1.Range("B" & iCount).Value <> " "
iCount = iCount + 1
Wend
End Sub

Thanks in advance for your help.

 

[Frank Kabel]

Hi
in your case remove the space between the apostrophes:
Private Sub CountRows()
Dim iCount As Long
iCount = 1
While Sheet1.Range("B" & iCount).Value <> ""
iCount = iCount + 1
Wend
End Sub


Or use
icount=application.counta(range("B:B"))

 

[Bob Phillips]

--

HTH

RP

 

[Bob Phillips]

Hugh,

Don't test for a space

Private Sub CountRows()
Dim iCount As Long
iCount = 1
While Sheet1.Range("B" & iCount).Value <> ""
iCount = iCount + 1
Wend
End Sub

 

[Trevor Shuttleworth]

Hugh

I think:

While Sheet1.Range("B" & iCount).Value <> ""

Note that the quotes are together as opposed to having a space in between.

Assuming that your data starts in row 1 and you want the last cell in column
B, you could use:

Dim iCount as Long
iCount = Range("B" & Rows.Count).End(xlUp).Row

That would be quicker if you have a lot of rows/cells to check.

Regards

Trevor

 

[Guest]

Thanks all for your reply. I did use "" instread of " "
before I asked help but it did not work. Now I know why
since data row was not started from row 1. The question
is than how to detect first data row? Thanks very much.

Hugh

 

[Hugh]

Thank all for your help. I used "" instead of " " before I
asked help. It did not work and now I know why since my
data rows was not started from row 1. The question is
then: how to detect first data row? Thanks again.

 

[Bob Phillips]

First data row

Private Sub CountRows()
Dim iCount As Long
iCount = 1
While Sheet1.Range("B" & iCount).Value = " "
iCount = iCount + 1
Wend
End Sub

 

[Guest]

Hi Bob,

Thanks very much for your help. Your method works fine.
You might mistyped " ". It should be "".

Hugh

 

[Bob Phillips]

Lol. After I made the same comment earlier as well.