How do I move cursor to next empty space??
 

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 ???

How do I move cursor to next empty space??
 

Cells(65536, "b").End(xlUp).Offset(1, -1).Select
James
WANNABE wrote:
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 ???

How do I move cursor to next empty space??
 

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....
=============================================
"Zone" wrote in message
oups.com...
Cells(65536, "b").End(xlUp).Offset(1, -1).Select
James
WANNABE wrote:
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 ???

How do I move cursor to next empty space??
 

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
WANNABE wrote:
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....
=============================================
"Zone" wrote in message
oups.com...
Cells(65536, "b").End(xlUp).Offset(1, -1).Select
James
WANNABE wrote:
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 ???

How do I move cursor to next empty space??
 

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.

--
Regards,
Tom Ogilvy


"WANNABE" wrote:

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....
=============================================
"Zone" wrote in message
oups.com...
Cells(65536, "b").End(xlUp).Offset(1, -1).Select
James
WANNABE wrote:
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 ???

How do I move cursor to next empty space??
 

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
=================================================
"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"WANNABE" wrote:

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....
=============================================
"Zone" wrote in message
oups.com...
Cells(65536, "b").End(xlUp).Offset(1, -1).Select
James
WANNABE wrote:
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 ???

How do I move cursor to next empty space??
 

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

WANNABE wrote:
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
=================================================
"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"WANNABE" wrote:

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....
=============================================
"Zone" wrote in message
oups.com...
Cells(65536, "b").End(xlUp).Offset(1, -1).Select
James
WANNABE wrote:
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 ???