Help creating macro to cut the last cell in a range with data to the top of the range

[Sinaloense]

Hi all,

I'm looking to create a macro look through a column and cut the last cell containing data and paste to the beginning of the data (top). I know I can do this manually dragging and dropping while holding Shift. I'm fairly new to excel and I would appreciate the help.

Thank you!

 

[mikenelena]

Hi,

Here is a code that will do what you have asked:

Sub MoveLastCellToTop()
    Dim lastRow As Long
    Dim columnToCheck As Integer
    
    ' Set the column number to check
    columnToCheck = 1 ' Change this to the column number you want to check
    
    ' Get the last row in the column
    lastRow = Cells(Rows.Count, columnToCheck).End(xlUp).Row
    
    ' Cut the value from the last cell and paste it to the top of the column
    Range(Cells(lastRow, columnToCheck), Cells(lastRow, columnToCheck)).Cut _
        Destination:=Range(Cells(1, columnToCheck), Cells(1, columnToCheck))
End Sub

If this solves your issue, please remember to mark your post as solved. Thanks!

...Mike

 

[Sinaloense]

Hi,

Here is a code that will do what you have asked:

Sub MoveLastCellToTop()
    Dim lastRow As Long
    Dim columnToCheck As Integer
   
    ' Set the column number to check
    columnToCheck = 1 ' Change this to the column number you want to check
   
    ' Get the last row in the column
    lastRow = Cells(Rows.Count, columnToCheck).End(xlUp).Row
   
    ' Cut the value from the last cell and paste it to the top of the column
    Range(Cells(lastRow, columnToCheck), Cells(lastRow, columnToCheck)).Cut _
        Destination:=Range(Cells(1, columnToCheck), Cells(1, columnToCheck))
End Sub

If this solves your issue, please remember to mark your post as solved. Thanks!

...Mike

Hey Mike,

The code does move the bottom cell to the top but it deleted the original top cell. How can I have the bottom cell rotate to the top without deleting?
Thank you for the quick response. I appreciate you.

 

[mikenelena]

My apologies. It's getting late and I missed that... This should get you there.

Sub MoveLastCellToTop()
    Dim lastRow As Long
    Dim columnToCheck As Integer
    
    ' Set the column number to check
    columnToCheck = 1 ' Change this to the column number you want to check
    
    ' Get the last row in the column
    lastRow = Cells(Rows.Count, columnToCheck).End(xlUp).Row
    
    ' Insert a new row at the top of the column
    Rows("1:1").Insert Shift:=xlDown
    
    ' Cut the value from the last cell and paste it to the top of the column
    Range(Cells(lastRow, columnToCheck), Cells(lastRow, columnToCheck)).Cut _
        Destination:=Range(Cells(1, columnToCheck), Cells(1, columnToCheck))
End Sub

 

[Peter_SSs]

What about this?

Sub BottomToTop()
  Const Col As String = "A" '<- Set your column of interest here
  
  Range(Col & Rows.Count).End(xlUp).Cut
  Range(Col & 1).Insert
End Sub

 

[Sinaloense]

What about this?

Sub BottomToTop()
  Const Col As String = "A" '<- Set your column of interest here
 
  Range(Col & Rows.Count).End(xlUp).Cut
  Range(Col & 1).Insert
End Sub

This works but how do I add another column to behave the same under the same macro?

 

[Peter_SSs]

Can you be more specific about exactly what the macro should do, and where it should do it?

 

[Sinaloense]

Can you be more specific about exactly what the macro should do, and where it should do it?

Hi Peter

I want the macro to scan column B (for example) from top to bottom. Identify first non-empty cell as the beginning of a list, find last cell of that list and move it to the beginning of the list. Then continue scanning down until it finds another list and does the same move.
I've attached an image to illustrate what the macro should do. Thanks in advance.

 

[Sinaloense]

Hi Peter

I want the macro to scan column B (for example) from top to bottom. Identify first non-empty cell as the beginning of a list, find last cell of that list and move it to the beginning of the list. Then continue scanning down until it finds another list and does the same move.
I've attached an image to illustrate what the macro should do. Thanks in advance.

 

[Peter_SSs]

Thanks for the clarification. I have still made 2 assumptions:

1. There is no heading like in your image and it is just the lists that appear in the column as per my first mini sheet below, and
2. The values in the column are not the result of formulas.

Sub BottomToTop_v2()
  Dim rA As Range
  
  Const Col As String = "B" '<- Set your column of interest here
  
  For Each rA In Columns(Col).SpecialCells(xlConstants).Areas
    rA.Cells(rA.Count).Cut
    rA.Cells(1).Insert
  Next rA
End Sub

Before

Sinaloense.xlsm
	B
1
2
3	1
4	2
5	3
6	4
7
8
9
10	A
11	B
12	C
13	D
14	E
15	F
16
17	cat
18	dog
19
Sheet1 (2)

After run 1

Sinaloense.xlsm
	B
1
2
3	4
4	1
5	2
6	3
7
8
9
10	F
11	A
12	B
13	C
14	D
15	E
16
17	dog
18	cat
19
Sheet1 (2)

After run 2

Sinaloense.xlsm
	B
1
2
3	3
4	4
5	1
6	2
7
8
9
10	E
11	F
12	A
13	B
14	C
15	D
16
17	cat
18	dog
19
Sheet1 (2)