Need to move last number in a column

[Ron Smith]

Sorry I haven't explained my problem that well. Here it is:

Line: Date Port 1 Port 2 Port 3
A B C D E F G H I
10
11 1/20 10 x x 20 x x 30
12 1/21 8 x x 22 x x 35
13 1/22 15 x x 18 x x 25
14 Last Line 1/23 12 x x 25 x x 20
15 (data added daily, or 5 rows at a time weekly)

20 12/31 14 15 40
21 1/23 12 25 20

What I want to know is how to move the 1/23 values in Row 14, Columns B, C,
F, I, etc. down to Row 21. The rows after Row 14 are added sometimes daily,
but more often weekly at which time I add 5 rows at a time. I want to move
the values in the last line entered down to row 21 (which of course will also
move down as I add more rows each day).

The "Last Line" is my invention. With the function ROW(A14), I can obtain
the number 14. So I wanted, for instance, to put the Cell Reference of
+C(=ROW(A14)), which would be "C14" in my mind, into C21. I found
that if I move the"Last Line" text/cell in Column 1 with a "Cut" and Paste,
the reference in the ROW function will change to the last/current location
of where Last Line is pasted. So all I would have to do is move "Last Line"
to the last row and I am in business.

It all works out in my mind, but Excel doesn't see it my way. Can anyone
tell me what I am doing wrong, or how to get this to work, or give me a
simple solution on how to tackle this priblem.

Thanks.

 

[Matthew Pfluger]

If I understand this right, you want to move the "Last Line"'s values to the
bottom of the sheet. I made a test sheet using the data you included and
programmed a macro to look for the words "Last Line" in column A. Then the
macro copies the data from that row to the end of the table.

Sub CopyRow()

Columns("A:A").Find(What:="Last Line", LookIn:=xlValues).Activate
Range(Cells(ActiveCell.Row, "B"), Cells(ActiveCell.Row, "I")).Select
Selection.Copy

Dim lLastRow As Long
lLastRow = Cells(Application.Rows.Count, 2).End(xlUp).Row

Range(Cells(lLastRow + 1, "B"), Cells(lLastRow + 1, "I")).Select
ActiveSheet.Paste

Application.CutCopyMode = False

End Sub

Try this script. I left the "Select" commands in the code so you could step
through it and watch what it is doing.

Let me know if this helps.
Matthew Pfluger

 

[Joel]

The best way of getting to the last line (14)
lastrow = Range("A1").end(xldown).row
the first cell A1 mustt be continuous down the worksheet without any empty
cells.

The simply copy the row
Rows(lastrow).copy destination:=Rows(21)
Then clear the last row
Rows(lastrow).clearcontents

 

[Rick Rothstein \(MVP - VB\)]

The best way of getting to the last line (14)

lastrow = Range("A1").end(xldown).row
the first cell A1 mustt be continuous down the worksheet
without any empty cells.

Or you could do this...

Cells(Rows.Count, "A").End(xlUp).Row

and not have to worry about the data being continuous or not. (Noting that
in both your code and mine, the reference is defaulting to the active
worksheet since a specific sheet reference was not made.)

Rick

 

[Joel]

with your code you will get row 21. hE WANTS ROW 15.

 

[Rick Rothstein \(MVP - VB\)]

hE WANTS ROW 15.

In re-reading it, and then re-reading it a couple of more times<g>, I think
you may be right. Sorry for adding to the confusion.

Rick

 

[Ron Smith]

Sorry guys, but I can't get any of your stuff to work. Are these
macros or what -- and do I have to do something special to get
them to work.

I assumed that these things like Cells and Range were functions,
but they aren't listed in my function list. I am on Excel 2000 --
does that make a difference or am I completely lost.

I would like to know what I am doing wrong, so if you can clue
me in, I would appreciate it.

Actually, I find that =LOOKUP(9999999,C10:C18) seems to work,
but I don't know why. The results always seems to be the last
value in the column, but the documentation seems to be telling
me that lookup will find the "largest" value in the array (if no
match with the target value) -- so I hesitate to depend on it.

Also, I have got the following function to work:
OFFSET(A14,0,2,1,1) seems to work (where A14 is the reference
to "Last Line". As noted in my original note, if I "Cut" and then
Copy Last Line to a different row in Column A, the reference in
the Offset function of A14 is changed to the new Row to where
I copied Last Line. I placed the OFFSET in Cell C21, and another
with ...0,5,1,1 in F21, ...0,8,1,1 in I21, and so forth. All I have to
do is move "Last Line" to the last line of my table and I seem to
be in business.

Even though I have gotten these two functions to work (even though
I am not sure why the LOOKUP function works), I would still like to
understand why I can't get your suggestions to work.

Thanks for the responses.

 

[Joel]

The solutions posted are for VBA code and not worksheet formulas.