Cut and Paste Row if "X" is in certain cell

ceereal

New Member
Joined
Oct 3, 2014
Messages
2
I am trying to create a macro that will cut a whole row if the cell in column K is equal to "Product". The Row that was cut must get pasted onto the next worksheet starting from row 2 onward. Also, I need all the cells from the first worksheet to move up once the row has been cut.

Please help.

Thanks,
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,852
Office Version
  1. 2019
Platform
  1. Windows
Try this:

Code:
Option Explicit


Sub DelK()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = Sheets("Sheet1"): Set sh2 = Sheets("Sheet2")
    Dim r1 As Long, r2 As Long
    r1 = sh1.Range("K" & Rows.Count).End(xlUp).Row
    Dim i As Long
    
    Application.ScreenUpdating = False
    For i = r1 To 1 Step -1
    r2 = sh2.Range("A" & Rows.Count).End(xlUp).Row
        If sh1.Range("K" & i) = "Product" Then
        sh1.Range("K" & i).EntireRow.Cut sh2.Range("A" & r2 + 1)
        sh1.Range("K" & i).EntireRow.Delete
        End If
    Next i
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    MsgBox ("completed")
    
End Sub
 

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Code:
Sub moveAndDelete()

    Dim start As Worksheet
    Dim dest As Worksheet
    
    Dim nextRow As Long
    nextRow = 2
    
    Set start = Sheets("Sheet11") 'CHANGE ME
    Set dest = Sheets("Sheet12") 'CHANGE ME


    With start
        For x = .Cells(Rows.Count, "K").End(xlUp).Row To 2 Step -1
            If .Cells(x, 11).Value = "Product" Then
                With .Cells(x, 1).EntireRow
                    dest.Cells(nextRow, 1).EntireRow.Value = .Value
                    .Delete
                End With
                nextRow = nextRow + 1
            End If
        Next x
    End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,985
Messages
5,526,063
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top