Find the last cell before/after an empty cell to copy it using a macro in an excel report

This code is just to look for the last cell then to copy its value in order to paste it into another sheet. I will show you 2 ways, looking from top to down and down to top, one works better than the other depending on what you want to do.

macro excel macro excel

 

When I use the macro ?

To search for the last cell then to copy it.

 

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

 

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

 

How is the macro ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

To find the last cell before a blank cell (looking from top to down) then paste:

Sub test()
' change Sheet1 by your sheet name where to copy
' change Sheet2 by your sheet name where to paste and A2 by the cell to paste
Worksheets("Sheet1").Range("A:A").End(xlDown).Copy Sheets("Sheet2").Range("A2")
End Sub

To find the last cell after a blank cell (looking from down to top) then paste it:

Sub test()
' change Sheet1 by your sheet name where to copy
' change Sheet2 by your sheet name where to paste and A2 by the cell to paste
Worksheets("Sheet1").Range("A65536").End(xlUp).Copy Sheets("Sheet2").Range("A2")
End Sub

Interesting Management