Cut and paste the value of cell to another cell in vba

Question

I need to transfer or move the value of Column F until last cell with value to Column D if Column C is eq to 'RRR'. I can't highlight or select the range starting from the Location of 'RRR' to the last cell with value 'SSS'. Instead, it select range from C4:C9 which is wrong.

    Dim ws As Worksheet, lRow As Long

Set ws = ThisWorkbook.ActiveSheet
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

Dim lCol As Long

With ws
    For x = 1 To lRow
        If .Cells(x, 3).Value = "RRR" Then
            lCol = Cells(x, Columns.Count).End(xlToLeft).Column
            Range("C" & x & ":C" & lCol).Select
        End If
    Next x
End With

Example:

Expected:

Can anyone tell me the problem in my code.

The reason that this didn't work: `Range("C" & x & ":C" & lCol).Select` is because you are saying, Range should be from (Column C and row x, i.e C4) to (Column C and the row which is equal to value lCol gives you, i.e C9) — Wizhi, Oct 23 '18 at 14:46

Wizhi · Accepted Answer · 2018-10-23T14:50:32.670

You are very near, only the select range that should be modified.

So you can build your range:

Range(A1:D1) -> Range(Cells(A1), Cells(D1)) -> 

Range(Cells(row number, column number), Cells(row number, column number)) -> 

Range(Cells(1, 1), Cells(1, 4))

This should do the trick:

Dim ws As Worksheet, lRow As Long
Dim x As Long

Set ws = ThisWorkbook.ActiveSheet
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

Dim lCol As Long

With ws
    For x = 1 To lRow
        If .Cells(x, 3).Value = "RRR" Then
            lCol = Cells(x, Columns.Count).End(xlToLeft).Column 'Find the last column number
            Range(Cells(x, 6), Cells(x, lCol)).Cut Cells(x, 4) 'Cut from row x and Column F (Column F = 6) to row x and column "lCol". Then paste the range into row x and column 4.
        End If
    Next x
End With

End Sub

You are welcome and thank you!!!, you did most of the work :)!! I want to give you one last advice. Declare all your variables (x wasn't declared). A good habit is to make VBA force variables, then you can't miss it. [How do I force VBA/Access to require variables to be defined?](https://stackoverflow.com/questions/1139321/how-do-i-force-vba-access-to-require-variables-to-be-defined). It will help you a lot when you are coding :). Good luck and happy coding :D!! — Wizhi, Oct 23 '18 at 15:09

score 3 · Answer 2 · edited Oct 23 '18 at 14:57

An alternative method would be to delete the cells in columns D and E

Dim ws As Worksheet, lRow As Long
Dim x As Long

    Set ws = ThisWorkbook.ActiveSheet
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

    Dim lCol As Long

    With ws
        For x = 1 To lRow
            If .Cells(x, 3).Value = "RRR" Then .Range("C" & x & ":D" & x).Delete Shift:=xlToLeft
            End If
        Next x
    End With

    End Sub

Cut and paste the value of cell to another cell in vba

2 Answers2