Find Last cell in Range when range is date format

[default105]

Excel 2k WinXP
I have tried the following but it returns $V$1:$V$3 when it should be
$V$3:$V$21 for this particular range.
Set PBVacRng = Sheet4.Range("V3:V" & Cells(Rows.Count,
"V").End(xlUp).Row)

However it works with this sheet but the format is set to custom m/d/yyyy;@
but it will not function correctly even if I change the format on the sheet
in question
Set HolidayRng = Sheet17.Range("A3:A" & Cells(Rows.Count, "A").End(xlUp).Row)


I know that with Application.Match You have to specify CDate, do you have to
do the same when specifying a range and if so, would you be kind enough to
show me how? Thanks in advance.

 

[default105]

I lied
Set HolidayRng = Sheet17.Range("A3:A" & Cells(Rows.Count, "A").End(xlUp).Row)
this does not work correctly it returns $A$3:$A$46 when it should be
$A$3:$A$12

 

[Mike H]

Hi,

The code you provided should work and if it doesn't return the result you
expect then it's because there are data in A46.

That data might not be visible, it could be a space or a formula that
returns a space.

Try this

=Len(A46) and if it returns anything except zero you have something in the
cell.

Mike

 

[default105]

It returns Zero (0). Any Ideas on what is happening or why?

 

[Mike H]

Maybe you could try it this way

r = Range("A:A").Find("*", [A1], xlValues, xlWhole, xlByRows, xlPrevious).Row
Set HolidayRng = Sheet17.Range("A3:A" & r)

Mike

 

[default105]

Riddle me this, I tried this and it works but WHY? It returns V3:V20

Dim LastRowT As Long
With Sheet4
LastRowT = .Cells(.Rows.Count, "V").End(xlUp).Row
End With

Set PBlVacRng = Sheet4.Range("V3:V" & LastRowT)

BUT

Set PBVacRng = Sheet12.Range("V3:V" & Cells(Rows.Count, "V").End(xlUp).Row)

Returns V1:V3

I am totally lost, You can not put in the .Cells(.Rows because vba calls .
an invalid character
Any Help