Find Last cell in Range when range is date format
 

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.

--
Pete Blackburn - words to live by:
"Don''t ever let somebody tell you. You can''t do something.You got a
dream,You gotta protect it." Edited Quote from the Pursuit Of Happiness

Find Last cell in Range when range is date format
 

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
--
Pete Blackburn - words to live by:
"Don''t ever let somebody tell you. You can''t do something.You got a
dream,You gotta protect it." Edited Quote from the Pursuit Of Happiness








"default105" wrote:

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.

--
Pete Blackburn - words to live by:
"Don''t ever let somebody tell you. You can''t do something.You got a
dream,You gotta protect it." Edited Quote from the Pursuit Of Happiness

Find Last cell in Range when range is date format
 

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" wrote:

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
--
Pete Blackburn - words to live by:
"Don''t ever let somebody tell you. You can''t do something.You got a
dream,You gotta protect it." Edited Quote from the Pursuit Of Happiness








"default105" wrote:

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.

--
Pete Blackburn - words to live by:
"Don''t ever let somebody tell you. You can''t do something.You got a
dream,You gotta protect it." Edited Quote from the Pursuit Of Happiness

Find Last cell in Range when range is date format
 

It returns Zero (0). Any Ideas on what is happening or why?
--
Pete Blackburn - words to live by:
"Don''t ever let somebody tell you. You can''t do something.You got a
dream,You gotta protect it." Edited Quote from the Pursuit Of Happiness


"Mike H" wrote:

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

Find Last cell in Range when range is date format
 

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" wrote:

It returns Zero (0). Any Ideas on what is happening or why?
--
Pete Blackburn - words to live by:
"Don''t ever let somebody tell you. You can''t do something.You got a
dream,You gotta protect it." Edited Quote from the Pursuit Of Happiness


"Mike H" wrote:

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

Find Last cell in Range when range is date format
 

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
--
Pete Blackburn - words to live by:
"Don''t ever let somebody tell you. You can''t do something.You got a
dream,You gotta protect it." Edited Quote from the Pursuit Of Happiness