End(xldown) not always going to last cell?

Hi all, Im using Range("f1").End(xlDown).Offset(1, 0).Select to get to
the next empty cell downwards in a column, It doesnt always work?
sometimes it selects the same cell over and over (im using this in
various instances).

is it something to do with the cell containing a number? also if the
collumn is blank then it crashes out, i have to have a line of 1s above
the column to make the code work.

is there a better way?


Duncan

Hi Duncan

> is there a better way?

Try:

Dim rng as Range

Set rng = Cells(Rows.Count, "A").End(xlUp)(2)

Additionally, it is rarely necessary to make selections: it is usually more
efficient to manipulate the range object.


---
Regards,
Norman


"Duncan" <[email protected]> wrote in message
news:[email protected]...
> Hi all, Im using Range("f1").End(xlDown).Offset(1, 0).Select to get to
> the next empty cell downwards in a column, It doesnt always work?
> sometimes it selects the same cell over and over (im using this in
> various instances).
>
> is it something to do with the cell containing a number? also if the
> collumn is blank then it crashes out, i have to have a line of 1s above
> the column to make the code work.
>
> is there a better way?
>
>
> Duncan
>

Hi Duncan,

you can use:

range("F" & application.rows.count).end(xlup).offset(1,0).select

If I understood your question correctly.

Regards,
Ivan

Brill, thats a lot better than before, also, I have changed it do
rng2.Value instead of selecting it and then doing activecell.value. I
think that will be faster.

Just another question.....im using rng2.Value = Now() but I could
really do with just the time and not the date, is that a command?

Duncan

Hi Duncan

> Just another question.....im using rng2.Value = Now() but I could
> really do with just the time and not the date, is that a command?


Try:

rng2.Value = Time()

---
Regards,
Norman

Many thanks, it appears I am as thick as I thought I was....i have used
time() before.........

Many thanks all

Duncan

To set the time:

Sub asdf()
Selection.NumberFormat = "[$-409]h:mm:ss AM/PM;@"
Selection.Value = Now() - Int(Now())
End Sub
--
Gary's Student


"Duncan" wrote:

> Brill, thats a lot better than before, also, I have changed it do
> rng2.Value instead of selecting it and then doing activecell.value. I
> think that will be faster.
>
> Just another question.....im using rng2.Value = Now() but I could
> really do with just the time and not the date, is that a command?
>
> Duncan
>
>