How do I move cursor to next empty space??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

WANNABE - 22 Sep 2006 18:18 GMT

I am trying to populate cells from multiple CSV files into one XLS file and
I need to locate the last cell with data in column B and move the cursor to
the next row down in column A. Can someone tell me how to locate that last
data cell ???

Reply to this Message

Zone - 22 Sep 2006 18:59 GMT

Cells(65536, "b").End(xlUp).Offset(1, -1).Select
James

> I am trying to populate cells from multiple CSV files into one XLS file and
> I need to locate the last cell with data in column B and move the cursor to
> the next row down in column A. Can someone tell me how to locate that last
> data cell ???

Reply to this Message

WANNABE - 22 Sep 2006 19:11 GMT

WOW!! Thanks James, That may be way over my head, but I will toy with it and
see what I can figure out... I have been trying something's that I am
familiar with and this is what I got to work...
Range("A2").Select
Do Until ActiveCell.Offset(0, 1).Value = ""
If ActiveCell.Value <> "" Then
ActiveCell.Offset(1, 0).Range("A1").Select
Else
ActiveCell.Offset(-1, 0).Copy
ActiveCell.PasteSpecial
ActiveCell.Offset(1, 0).Range("A1").Select
End If
Loop
I was planning to post this and ask if there might be a better way, and I
think you have supplied that. Any additional notes would be helpful, I'm
sure you can tell from the code I pasted, what it is that I am doing.
Thanks Again!!!
AGAIN >> WOW one line, I never fail to be amazed....
=============================================

> Cells(65536, "b").End(xlUp).Offset(1, -1).Select
> James
[quoted text clipped - 5 lines]
>> last
>> data cell ???

Reply to this Message

Zone - 22 Sep 2006 19:46 GMT

Wannabe, Glad to be of help. The End method is tried and true, and you
seem to understand the offset method. One thing, I'm looking from the
bottom up and you are looking from the top down. The two ways of
looking would be equivalent if there were no blanks in between the top
and bottom. If there are blanks in beween, my method and yours will
select different cells. I'm not sure why you want to copy data from
column B into the first empty cell in column A. If I can help you
further, post back.
Regards,
James

> WOW!! Thanks James, That may be way over my head, but I will toy with it and
> see what I can figure out... I have been trying something's that I am
[quoted text clipped - 24 lines]
> >> last
> >> data cell ???

Reply to this Message

Tom Ogilvy - 22 Sep 2006 20:05 GMT

go to the first empty column you have. then click the End button, then the
down arrow. This takes you to the bottom of the sheet. Now click on B65536

now click on End, then the Up Arrow. That is what this part of the code does:

Cells(65536, "b").End(xlup)

You should be able to figure out the rest.

Signature

Regards,
Tom Ogilvy

Reply to this Message

WANNABE - 22 Sep 2006 21:56 GMT

Thank you Both, James and Tom!! I will try to respond to both prompts in
this one.
James, I think you mis-read my post. It copies cells from column A down
until there is a blank in column B. I am however very glad that you ask the
question, because Originally that is not what I wanted to have to do, I only
did because I thought I would need to have the A column filled ***** ANYWAY
I had fallen off track of my original plan... back on track now,,

Tom, once I pasted that into a new macro and stepped through it I began to
look at the pieces of it and could see how it uses the END button option
that I use manually "ALL THE TIME" just never thought of how it could be
used in code.. ALSO the XL?? I know there are many of those, but they are
outside the limits of my current knowledge.
Thank you both again very much for your time..
FYI>> Here is how I am using what you have shown me.

ACTVCL = Cells(65536, "B").End(xlUp).Offset(1, -1).Address
Range(ACTVCL).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range(ACTVCL).Select
THANKS AGAIN
=================================================

> go to the first empty column you have. then click the End button, then
> the
[quoted text clipped - 39 lines]
>> >> last
>> >> data cell ???

Reply to this Message

Zone - 23 Sep 2006 15:58 GMT

Wannabe, I should have explained this better, so I'm glad Tom went into
a bit further. End can look from any cell in any of the 4 directions
and will look until it finds a non-empty cell. The four directions are
xlUp, xlDown, xlToLeft and xlToRight. For instance, if you wanted to
find the last cell with something in it in row 1, you could use
Cells(1,"iv").End(xlToLeft).address
And of course, offset simply offsets by the numbers in its arguments,
the first being the rows and the second being the columns to offset by.
Glad we could help. James

> Thank you Both, James and Tom!! I will try to respond to both prompts in
> this one.
[quoted text clipped - 67 lines]
> >> >> last
> >> >> data cell ???

Reply to this Message