CODEWITHSUNDEEP
vbaexcelms-office
mjkutzman
mjkutzman

Reputation: 27

VBA - Cut the last entry from each row and paste into a new column. Length of rows varies

I'm a new user and can't embed images yet... please see the links.

Say my data looks like this: Before. And, I'd like it to look like this: After. Here's the code I've been trying (unsuccessfully):

Dim lastRow As Long
lastRow = Range("Sheet2!A" & Rows.Count).End(xlUp).Row
Dim col As Integer
col = Range("Sheet2!A1:A" & lastRow).End(xlToRight).Column
Columns(col).Copy
Columns(col + 1).Insert Shift:=xlToRight

I'd like the macro to work on a dataset with any number of rows, hence the lastRow stuff. Any ideas?

Upvotes: 0

Views: 134

Answers (1)

BruceWayne
BruceWayne

Reputation: 23285

How's this work?

Sub move_data()
Dim lastRow As Long, lastCol As Long
Dim ws As Worksheet

Set ws = Worksheets("Sheet2")
lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
lastCol = ws.UsedRange.Columns.Count

Dim i As Long
For i = 1 To lastRow
    If ws.Cells(i, lastCol) = "" Then
        ws.Cells(i, ws.Cells(i, 1).End(xlToRight).Column).Cut ws.Cells(i, lastCol)
    End If
Next i

End Sub

I hesitate to use UsedRange, but if your data does exist in a "block" that should work okay. Basically, it just checks each row, and if that last column is empty, move the last cell from column A to that column.

Upvotes: 1

Related Questions