End(xlDown) syntax

[SGBCleve]

I am using Excel 2010 and having trouble with using a Range.End(xlDown) statement not being recognized.

I have tried Range("B4, Range("B4").End (xldown)).Count
Range(Range("B4"), Range("B4").End(xlDown)).Count
and a variety of other attempts.
Obviously, the first value in the Range is Range("B4") and the second value is Range ("B4").End(xlDown)
Do I need another quotation mark before the .Count statement?

This is driving me crazy. Thank you

 

[Norie]

Not sure what you are trying to do but if you are looking for a count of cells from B4 down you could use something like this.

Dim cnt As Long

    cnt = Range("B4", Range("B4").End(xlDown)).Count

    MsgBox cnt & " cells in column B."

 

[Kenneth Hobson]

Your 2nd was fine though not typically done that way.

Sub Test()
  MsgBox Range("B4", Range("B4").End(xlDown)).Count
  MsgBox Range(Range("B4"), Range("B4").End(xlDown)).Count
  MsgBox Range([B4], [B4].End(xlDown)).Count
  MsgBox Range("B4", Cells(Rows.Count, "B").End(xlUp)).Count
End Sub

 

[SGBCleve]

Yes, that is what I am looking for, but for some reason it gives me needs separator at the second cell address.
I have it written as
Counter = Sheets(i).Range("K2, Range("K2").End(xlDown)).Count and it won't compile. It says needs list separator or parenthesis.

 

[Kenneth Hobson]

See the examples. You did not fully quote the first K2.

 

[SGBCleve]

All the Test Subroutines work. Not sure why it wouldn't compile in my code and got stuck with Range("K2"). Will try again and make sure all other windows are closed, but I did specify the worksheet.

 

[Norie]

In this code it should be "B4" not just "B4.

Range("B4, Range("B4").End (xldown)).Count

 

[SGBCleve]

I tried it with quotes before, too. But that must have been the error. I am on to the next error now
Thank you.