Copying the last three cells with data from a row into new cells

[StraxusD]

I have a table of data that has at least 60 rows in it that tracks the newest installed gas cylinder on some test equipment. Each piece of equipment has its on row and as we change the bottles, we enter the new cylinder # and reference # to the end of the row. Because some of the equipment uses much more quickly than others, some of the equipment is on its first bottle of the year and some is on its third already. What I want to do is scan each row (starting at row 3, column "I") in columns "I" through "AA" and find the last two columns of data and copy it to columns "C" and "D" of the same row so that when we audit the bottles the latest bottle is listed at the beginning of the sheet to make tracking easier. The data is not always numbers, it is text as well.

I am not versed in VBA at all, but I figured it was the easiest way to accomplish this task. Can anyone help me with this? I want it to be open ended enough that if I add more rows the program will not have to be modified.

 

[My Aswer Is This]

Try this:
It searches each row for the last two values in the row does not stop at column (AA)
See if this will work for you.

Sub Search_Me()
'Modified 5/13/2022  10:59:16 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim LastColumn As Long
Lastrow = Cells(Rows.Count, "I").End(xlUp).Row

    For i = 3 To Lastrow
        LastColumn = Cells(i, Columns.Count).End(xlToLeft).Column
        Cells(i, 4).Value = Cells(i, LastColumn).Value
        Cells(i, 3).Value = Cells(i, LastColumn - 1).Value
    Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

My script does this:
You said this:
and find the last two columns of data and copy it to columns "C" and "D"

But your subject title says:
Copying the last three cells with data from a row into new cells

 

[StraxusD]

My script does this:
You said this:
and find the last two columns of data and copy it to columns "C" and "D"

But your subject title says:
Copying the last three cells with data from a row into new cells

I didn't realize that I put three columns in the title, but your script does exactly what I needed it to do. This will make it way easier for my guys to audit all of the gas cylinders since the checklist will be on a single sheet of paper now. Thank you so much! Just curious, if we ever decide to modify the sheet and add the expiration date into the data, how would I modify the program to copy the third from the last column over?

Would I just add the line "Cells(i, 2).Value = Cells(i, LastColumn - 2).Value" in the loop?

Thank you again!!