Copy paste range

Question

The macro below matches the contents in Sheet1 A2 with a row in Sheet2 Column A.

For the final step I want to also make it select the row below the matching cell in Sheet2 Column A down to the last row with data then over to Column M, cut that range, and paste it up one row so instead of deleteing the matching row it just gets pasted over. Can you help?

Sub Moveup()

Dim ws1 As Worksheet

Dim ws2 As Worksheet

Dim SearchRow As Range

Dim SearchValue As Range

Dim lngRow As Long

Set ws1 = ThisWorkbook.Worksheets("Sheet1")

Set ws2 = ThisWorkbook.Worksheets("Sheet2")

Set SearchValue = ws1.Range("A2") 'Needs to be a range object or last 2 lines will error

Set SearchRow = ws2.Range("A:A").Find(What:=SearchValue.Value, _

LookIn:=xlValues, _

LookAt:=xlWhole, _

SearchOrder:=xlByRows, _

SearchDirection:=xlNext, _

MatchCase:=False, _

SearchFormat:=False)

If Not SearchRow Is Nothing Then

lngRow = SearchRow.Row

'Final Step

End If

End Sub

Answer 1

Hi Amana

Try the following code

''''****************************************************************************

Sub Moveup()

Dim ws1 As Worksheet

Dim ws2 As Worksheet

Dim MatchCell As Range

Dim SearchValue As Range

Dim NextRow As Long

Dim LastRow As Long

Set ws1 = ThisWorkbook.Worksheets("Sheet1")

Set ws2 = ThisWorkbook.Worksheets("Sheet2")

Set SearchValue = ws1.Range("A2") '''Needs to be a range object or last 2 lines will error

With ws2

    ''' Find the value/cell 

    Set MatchCell = .Range("A:A").Find(What:=SearchValue.Value, \_ 

    LookIn:=xlValues, \_ 

    LookAt:=xlWhole, \_ 

    SearchOrder:=xlByRows, \_ 

    SearchDirection:=xlNext, \_ 

    MatchCase:=False, \_ 

    SearchFormat:=False) 

    If Not MatchCell Is Nothing Then  ''' If value is found 

            NextRow = MatchCell.Row + 1    ''' the row below the matching cell 

            LastRow = Cells(Rows.Count, "A").End(xlUp).Row  '' the last row with data in column A 

            .Range(Cells(NextRow, "A"), Cells(LastRow, "M")).Cut MatchCell   ''' Cut and Paste over the Range 

    End If 

End With

End Sub

'''''''''**********************************************************************

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 2

There is absolutely no difference between deleting part of a row and and shifting the range below it up by one row.

Just use this in place of 'Final Step

ws2.Range("A" & SearchRow).Resize(1,13).Delete Shift:=xlUp