Copy last non empty cell in column A and paste with offset.

[Dawid18db]

Hello everyone,

I want to find and copy last non empty cell in column A and paste it with offset. Problem is that in column A are formulas (Range A1:A18).
For range A1:A8 formulas are numbered. I want copy last cell with numbered formula, which in this case is A8. Unfortunately code copy cell A18, which is last cell with formula.

Code is as follows:
Sub test()
Range("A" & Rows.Count).End(xlUp).Copy
Range("A" & Rows.Count).End(xlUp).Offset(0, 8).PasteSpecial (xlPasteValues)

End Sub

I attached excel file.

Could you help me?

[nankw83]

Hi Dawid18db & welcome to the forum,

Try the below code

Please Login or Register  to view this content.

[Fluff13]

Hi & welcome to the board.
How about

Please Login or Register  to view this content.

[nankw83]

After seeing Fluff's code & re-reading your post, if you're only looking for the last cell you need to change the below line

@Fluff, nice simple code

Please Login or Register  to view this content.

[Sintek]

Another Option perhaps...

Please Login or Register  to view this content.

[Dawid18db]

In the meantime I kept fighting to solve this problem myself. Successfully! Code below.

Sub test()
Cells(Application.Evaluate("MAX(IF(A1:A2000<>"""",ROW(A1:A2000)),0,1)"), "A").Copy
Cells(Application.Evaluate("MAX(IF(A1:A2000<>"""",ROW(A1:A2000)),0,1)"), "A").Offset(0, 8).PasteSpecial (xlPasteValues)
End Sub

I also tested your propositions - all of them working properly. Many thanks.

[nankw83]

Glad we were able to help ... If that takes care of your question, please mark this thread as [SOLVED] from the Thread Tools above your first post & consider Add Reputation to those who have helped by clicking the star under their usernames