# Thread: Find last value in row and move to column

1. ## Find last value in row and move to column

Hello all,

I was wondering if anyone could me with a formula that can do the following

1. Find last cell with a value in row
2. Move last cell with value to desired column

And this formula or VBA do this for all 15000+ rows.

The data looks like the following little illustration (each number represents a column)

Row 1: 12345
Row 2: 1234567
Row 3: 123456789

And I need it do to this

Row 1: 1234 5
Row 2: 123456 7
Row 3: 12345789 9

Numbers 5, 7, 9 would be in the same column

Any help would be greatly appreciated.

2. I don't understand the request. The sample is not clear. Can you try again? Maybe post a small sample workbook.

3. No problems, Thanks for your interest - I have attached a small sample workbook

I think I may have found close to what I am looking for - =OFFSET(A1,0,MATCH(MAX(A1:E1)+1,A1:E1,1)-1)

However I need this to return text values, as this formula only returns number values.

Current

 A B C D E F G H 1 Text Value 1 Text Value 2 Text Value 3 Text Value 4 2 Text Value 1 Text Value 2 Text Value 3 3 Text Value 1 Text Value 2 4 Text Value 1 Text Value 2 Text Value 3 Text Value 4 5 Text Value 1 Text Value 2 Text Value 3

Hopeful Result from formula

 A B C D E F G H 1 Text Value 1 Text Value 2 Text Value 3 Text Value 4 2 Text Value 1 Text Value 2 Text Value 3 3 Text Value 1 Text Value 2 Text Value 2 4 Text Value 1 Text Value 2 Text Value 3 Text Value 4 5 Text Value 1 Text Value 2 Text Value 3

So the last value of each row will now be column H in the same row

Hope that is okay, thanks again for having a look. I know the formula i have above is so close, I just need it to work with words, not numbers

Cheers

4. Apologies, the in B3 of the 2nd table, I should have deleted "Text Value 2" as it now appears in H... small oversight

5. Note that a formula will not be able to physically transfer the row's last values as you have shown. A formula will only be able to populate column H with the last value, but the last value will still remain in the original location.

You will need VBA to actually remove it.

In the meantime, you can use a formula in column H like:

=LOOKUP(2,1/(A1:G1<>""),A1:G1)

copied down.

6. Thankyou kind sir.. this worked and very efficient... thank you mate.

If by chance, and only if not asking too much, could provide some sort of VBA, that would be great - the closest I got is below with VBA, but this only navigated to the last populated cell, and I didn't get as far as moving then deleting from original cell

As you can tell... new to this.

Sub LastCellBeforeBlankInRow()Range("A1").End(xlToRight).SelectEnd Sub

7. Try:

Code:
```Sub LastCellBeforeBlankInRow()
Dim i As Long
With Sheets("Sheet1")
For i = 1 To .UsedRange.Rows.Count
.Cells(i, .Columns.Count).End(xlToLeft).Cut .Range("H" & i)
Next i
End With

End Sub```
replacing "Sheet1" for actual sheetname.

8. An alternative

Code:
```Public Sub MoveAlong()
Dim lastcol As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

For i = 1 To .Range("A1").End(xlDown).Row

lastcol = .Cells(i, "A").End(xlToRight).Column
.Cells(i, lastcol).Resize(, 8 - lastcol).Insert Shift:=xlToRight
Next i
End With

Application.ScreenUpdating = True
End Sub```

9. If it's coming from Bob, it's a better alternative

I forgot the Application.ScreenUpdating... always should be included.

10. Originally Posted by NBVC
If it's coming from Bob, it's a better alternative
No better, just wanted to show how to dynamically determine the column count - seemed worth it for that.

Originally Posted by NBVC
I forgot the Application.ScreenUpdating... always should be included.
Always is, make it far less visually traumatic, and can dramatically improve performance (I know you know that NBVC, for the OP's edification )

Page 1 of 2 1 2 Last