CODEWITHSUNDEEP
excelvba
Yoona May
Yoona May

Reputation: 93

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

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: enter image description here

Expected:

enter image description here

Can anyone tell me the problem in my code.

Upvotes: 2

Views: 1141

Answers (2)

cybernetic.nomad
cybernetic.nomad

Reputation: 6418

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

Upvotes: 3

Wizhi
Wizhi

Reputation: 6549

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

Upvotes: 4

Related Questions