Copy/Paste to next Cell if A:A <> 1

[tuongtu3]

Hello All,
I need help to COPY/PASTE if value in COLUMN A:A <> 1. Please see sample attached.

Regards,
tt3

[dflak]

Copy what? Will the 1's always be contiguous as in the example?

As best I can tell, you take the last row that doesn't have a 1 (row 8) and copy and paste it to the end of the row above it (row 7). Then you take the row above it (row 7 in the example) and paste it to the end of the row above it (row 6 in the example). You stop appending because the row above it (row 5) has a 1 in column A.

In theory you could keep copying and pasting until you run out of rows.

Have I described what you want sufficiently?

[tuongtu3]

Hi dflak,

Will the 1's always be contiguous as in the example? <=== YES
and it stop at row 5 becuase its has a 1 in column A.

Regards,
tt3

[dflak]

Here is the "magic"

Please Login or Register  to view this content.

[Alf]

A slightly different approach.

Please Login or Register  to view this content.

Alf

[Alf]

Hi tt3

Thanks for feedback and rep.

My macro is "build" on the assumption that column A either contains 1 or nothing whereas dflak's code checks if the value in column A really is 1. So if your uploaded file had 2 in cell A6 my code would only copy values in row 8 to row 7 where dflak's code would copy row 7 and 8 to row 6 and row 8 to row 7. As I'm not really sure what you wanted I thought it was best to point out the differences so you can select the code that suit your need best.

Hi deflak
Liked the way you included comments in you code, a thing I shall try to adapt to as well in the future. By the way I think you forgot to add a "Application.CutCopyMode = False" at the end of your code.

Alf

[dflak]

I've been coding since the 1980's. I've gotten into the habit of writing comments. In fact, I start with the comments tell me what I have to do and then fill in with code.

[tuongtu3]

Hi Alf,
Both codes are very good. First I modified code from "dflak" so code can loop and stop at cell A1 to find "1". Can you modify your code to do so (loop up to Cell A1) and please note that I also need to copy from BLANK CELL to upper CELL contain "1" or ANY other number. Please see sample.

Regards,
tt3

[Alf]

Test this code and see if it works the way you wish.

Please Login or Register  to view this content.

Alf

Ps Merry Christmas

[tuongtu3]

Hi Alf,
That's perfect and thank you very much for your time.

Regards,
tt3
Ps Merry Christmas

[Alf]

Thanks for feedback and rep.

Regards

Alf