Macro to Cut Last Column with Data

ExcelDean

New Member
Joined
Mar 31, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi! I’m a beginner trying to program a macro that will cut the last cell in a row with data present and paste it to a specific cell in that same row, before going to the next line and looping. I’ve run into all sorts of issues trying to record the macro and programming it myself. Some cells are empty before the final cell with data and the amount of columns changes from row to row but I always need to paste it to the same columns (in this case S & T). Please let me know if my description is unclear, any and all help is appreciated. Thanks!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Assuming your data starts in A1 try this.
VBA Code:
Dim rw As Range
Dim cell As Range

    For Each rw in Range("A1").CurrentRegion
        Set cell = Cells(rw.Row, Columns.Count).End(xlToLeft)
        cell.Copy Cells(rw.Row, "S").Resize(,2)
   Next rw
 

ExcelDean

New Member
Joined
Mar 31, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Assuming your data starts in A1 try this.
VBA Code:
Dim rw As Range
Dim cell As Range

    For Each rw in Range("A1").CurrentRegion
        Set cell = Cells(rw.Row, Columns.Count).End(xlToLeft)
        cell.Copy Cells(rw.Row, "S").Resize(,2)
   Next rw
Hi, thanks so much for the quick reply. I could be doing something wrong (or missing a basic step) but when I run this Excel goes to not responding and the macro doesn’t complete. Thanks again!
 

ExcelDean

New Member
Joined
Mar 31, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Assuming your data starts in A1 try this.
VBA Code:
Dim rw As Range
Dim cell As Range

    For Each rw in Range("A1").CurrentRegion
        Set cell = Cells(rw.Row, Columns.Count).End(xlToLeft)
        cell.Copy Cells(rw.Row, "S").Resize(,2)
   Next rw
Hi Norie, I played around with this some more today and got it working. Sorry, this isn’t what I was looking for. I’m trying to make a macro that goes line by line to find the last column with data in each row (the amount of columns changes from row to row). After copying/cutting the last cell with data I need to paste it to the S and T columns before going to the next row and repeating the process. Thank you!
 

ExcelDean

New Member
Joined
Mar 31, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Norie, I played around with this some more today and got it working. Sorry, this isn’t what I was looking for. I’m trying to make a macro that goes line by line to find the last column with data in each row (the amount of columns changes from row to row). After copying/cutting the last cell with data I need to paste it to the S and T columns before going to the next row and repeating the process. Thank you!
Sorry, disregard that!! I went back to a previous macro that I made by accident.

The code you wrote works but doesn’t move to the next line. I’m going to play with it to see if I can get it there but if you have a simple solution that would be amazing!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Try this adjustment to the code I posted.
VBA Code:
Dim rw As Range
Dim cell As Range

    For Each rw in Range("A1").CurrentRegion.Rows
        Set cell = Cells(rw.Row, Columns.Count).End(xlToLeft)
        cell.Copy Cells(rw.Row, "S").Resize(,2)
   Next rw
 
Solution

ExcelDean

New Member
Joined
Mar 31, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Try this adjustment to the code I posted.
VBA Code:
Dim rw As Range
Dim cell As Range

    For Each rw in Range("A1").CurrentRegion.Rows
        Set cell = Cells(rw.Row, Columns.Count).End(xlToLeft)
        cell.Copy Cells(rw.Row, "S").Resize(,2)
   Next rw
Perfect, thank you so much!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,321
Messages
5,641,513
Members
417,213
Latest member
wikk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top