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.

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"))


--
Regards
Frank Kabel
Frankfurt, Germany


Hugh wrote:
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.

--

HTH

RP

"Hugh" wrote in message
...
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.

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

--

HTH

RP

"Hugh" wrote in message
...
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.

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


"Hugh" wrote in message
...
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.

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

-----Original Message-----
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.
























.

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.


-----Original Message-----
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.
























.

First data row

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


--

HTH

RP

"Hugh" wrote in message
...
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.


-----Original Message-----
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.
























.

Hi Bob,

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

Hugh

-----Original Message-----
First data row

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


--

HTH

RP

"Hugh" wrote in
message
...
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.


-----Original Message-----
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.
























.



.

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

--

HTH

RP

wrote in message
...
Hi Bob,

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

Hugh

-----Original Message-----
First data row

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


--

HTH

RP

"Hugh" wrote in
message
...
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.


-----Original Message-----
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.
























.



.