VBA - search data, delete it from current worksheet and paste to a new worksheet

I

iris1007

New Member
Joined
Jun 28, 2017
Messages
31
Hi,
I have a huge data which I need to search for Vendor with "delete" note, delete the whole row from the current worksheet and paste the whole row data into a new worksheet.
Below is the sample data:
Original - before the deletion - Sheet1

[TABLE="width: 325"]
<colgroup><col width="129" style="width: 97pt; mso-width-source: userset; mso-width-alt: 4579;"> <col width="181" style="width: 136pt; mso-width-source: userset; mso-width-alt: 6428;"> <col width="122" style="width: 92pt; mso-width-source: userset; mso-width-alt: 4352;"> <tbody>[TR]
[TD="width: 129, bgcolor: transparent"]Vendors[/TD]
[TD="width: 181, bgcolor: transparent"]Part #[/TD]
[TD="width: 122, bgcolor: transparent"]Ref #[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]123FDG[/TD]
[TD="bgcolor: transparent"]071-50001-8102[/TD]
[TD="bgcolor: transparent"]F149120021
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]123FDG[/TD]
[TD="bgcolor: transparent"]071-50001-8102[/TD]
[TD="bgcolor: transparent"]F149120021[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]123FDG[/TD]
[TD="bgcolor: transparent"]071-50001-8102[/TD]
[TD="bgcolor: transparent"]F149120021[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]123FDG to delete[/TD]
[TD="bgcolor: transparent"]071-50001-8102[/TD]
[TD="bgcolor: transparent"]F149120021[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ABC[/TD]
[TD="bgcolor: transparent"]0145-5521-1145[/TD]
[TD="bgcolor: transparent"]37Z-15142396[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ABC[/TD]
[TD="bgcolor: transparent"]0145-5521-1145[/TD]
[TD="bgcolor: transparent"]37Z-16548768[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ABC - to delete[/TD]
[TD="bgcolor: transparent"]0145-5521-1145[/TD]
[TD="bgcolor: transparent"]37Z-138804512[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ASDFR123[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]81A-CBB26680[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ASDFR123[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]81A-CDD27477[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]1145214[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]2255551420[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]22523413G67[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]22523413G67[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]22523413G67[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM - to delete[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]22502171[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]225762201D[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]225F0991[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]22611AD123[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]22611AD123[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]ABC22622[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]F23201884[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM - to delete[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]2255551420[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM - to delete[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]225F0991[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OCBBC[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]2-A4276-00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OCBBC[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]2-A4276-00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OCBBC[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]81A-CAA26474[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OCBBC - to delete[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]81A-CBB26680[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]XYZ12[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]F23201884[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]XYZ12[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]F23201884[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]XYZ12[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]A23267[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]XYZ12[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]ZX-10-23809[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]XYZ12[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]ZX-10-23809[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]XYZ12 - to delete[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]A23267[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1 - after deletion
[TABLE="width: 325"]
<colgroup><col width="129" style="width: 97pt; mso-width-source: userset; mso-width-alt: 4579;"> <col width="181" style="width: 136pt; mso-width-source: userset; mso-width-alt: 6428;"> <col width="122" style="width: 92pt; mso-width-source: userset; mso-width-alt: 4352;"> <tbody>[TR]
[TD="width: 129, bgcolor: transparent"]Vendors[/TD]
[TD="width: 181, bgcolor: transparent"]Part #[/TD]
[TD="width: 122, bgcolor: transparent"]Ref #[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]123FDG[/TD]
[TD="bgcolor: transparent"]071-50001-8102[/TD]
[TD="bgcolor: transparent"]F149120021[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]123FDG[/TD]
[TD="bgcolor: transparent"]071-50001-8102[/TD]
[TD="bgcolor: transparent"]F149120021[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]123FDG[/TD]
[TD="bgcolor: transparent"]071-50001-8102[/TD]
[TD="bgcolor: transparent"]F149120021[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ABC[/TD]
[TD="bgcolor: transparent"]0145-5521-1145[/TD]
[TD="bgcolor: transparent"]37Z-15142396[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ABC[/TD]
[TD="bgcolor: transparent"]0145-5521-1145[/TD]
[TD="bgcolor: transparent"]37Z-16548768[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ASDFR123[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]81A-CBB26680[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ASDFR123[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]81A-CDD27477[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]1145214[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]2255551420[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]22523413G67[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]22523413G67[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]22523413G67[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]225762201D[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]225F0991[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]22611AD123[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]22611AD123[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]ABC22622[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]F23201884[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OCBBC[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]2-A4276-00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OCBBC[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]2-A4276-00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OCBBC[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]81A-CAA26474[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]XYZ12[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]F23201884[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]XYZ12[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]F23201884[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]XYZ12[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]A23267[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]XYZ12[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]ZX-10-23809[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]XYZ12[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]ZX-10-23809[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 (NEW)

[TABLE="width: 339"]
<colgroup><col width="146" style="width: 110pt; mso-width-source: userset; mso-width-alt: 5205;"> <col width="203" style="width: 152pt; mso-width-source: userset; mso-width-alt: 7224;"> <col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3640;"> <tbody>[TR]
[TD="width: 146, bgcolor: transparent"]Vendors[/TD]
[TD="width: 203, bgcolor: transparent"]Part #[/TD]
[TD="width: 102, bgcolor: transparent"]Ref #[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]123FDG to delete[/TD]
[TD="bgcolor: transparent"]071-50001-8102[/TD]
[TD="bgcolor: transparent"]F149120021[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ABC - to delete[/TD]
[TD="bgcolor: transparent"]0145-5521-1145[/TD]
[TD="bgcolor: transparent"]37Z-138804512[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM - to delete[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]22502171[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM - to delete[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]2255551420[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]IBM - to delete[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]225F0991[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OCBBC - to delete[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]81A-CBB26680[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]XYZ12 - to delete[/TD]
[TD="bgcolor: transparent"]07F10-150326018[/TD]
[TD="bgcolor: transparent"]A23267[/TD]
[/TR]
</tbody>[/TABLE]

Hope anyone there can help. Thanks!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Hi
try following code & see if does what you want


Place in STANDARD module

Code: 
Sub CopyDeleteRows()
    Dim c As Range, CopyRange As Range, DataRange As Range
    Dim DestRange As Range


    With ThisWorkbook
'source sheet
        With .Sheets("Sheet1")
            Set DataRange = .Range(.Range("A2"), .Range("A" & .Rows.Count).End(xlUp))
        End With
'destination Sheet
        With .Sheets("Sheet2") '
            Set DestRange = .Range("A2")
        End With
    End With
    
    DataRange.EntireRow.Hidden = False
    
    For Each c In DataRange.Cells
        If UCase(Right(c.Value, 6)) = "DELETE" Then
            If CopyRange Is Nothing Then
                Set CopyRange = c
                    Else
                Set CopyRange = Union(CopyRange, c)
            End If
        End If
    Next c
    
    If Not CopyRange Is Nothing Then
    
    With CopyRange.EntireRow
        .Copy DestRange
        .Delete shift:=xlShiftUp
    End With
    End If
End Sub


Dave
 
Upvote 0
Hi Dave. This was just what I was looking for so thank you.

However, when I copy to another sheet I would like to use copy values only rather than taking the functions - I'm copying from a table and don't want the references to be copied.

Kent
 
Upvote 0
@Kentman
Please do not "hijack" other peoples threads. If you need help please start a thread of your own. You can always add a link back to this post if you feel it's relevant.
 
Upvote 0
Sorry Fuff. Didn't think I was hijacking just thought it was expanding on the question as it would extend the answer for others and it was to the person who added the solution. Will post new thread.
 
Upvote 0
Hi Dave,
Thank you so much for the helps. 　
If the "DELETE" column is not in "A" but in "C", can I still search and delete the whole row and copy it to Sheet2?　

Also, as I will be running this report every two weeks, the new "DELETE" row will be paste to Sheets2 the next empty row?
Thanks!

 
Upvote 0
iris1007 said:
Hi Dave,
Thank you so much for the helps. 　
If the "DELETE" column is not in "A" but in "C", can I still search and delete the whole row and copy it to Sheet2?　

Also, as I will be running this report every two weeks, the new "DELETE" row will be paste to Sheets2 the next empty row?
Thanks!
Click to expand...

Hi,
Try this update

Rich (BB code): 
Sub CopyDeleteRows()
    Dim c As Range, CopyRange As Range, DataRange As Range
    Dim DestRange As Range
    Dim SearchColumn As String


    SearchColumn = "A"


    With ThisWorkbook
'source sheet
        With .Sheets("Sheet1")
            Set DataRange = .Range(.Range(SearchColumn & "2"), .Range(SearchColumn & .Rows.Count).End(xlUp))
        End With
'destination Sheet
        With .Sheets("Sheet2") '
            Set DestRange = .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row + 1, 1)
        End With
    End With
    
    DataRange.EntireRow.Hidden = False
    
    For Each c In DataRange.Cells
        If UCase(Right(c.Value, 6)) = "DELETE" Then
            If CopyRange Is Nothing Then
                Set CopyRange = c
                    Else
                Set CopyRange = Union(CopyRange, c)
            End If
        End If
    Next c
    
    If Not CopyRange Is Nothing Then
    
    With CopyRange.EntireRow
        .Copy DestRange
        .Delete shift:=xlShiftUp
    End With
    End If
End Sub

I have added variable to allow you to set which column you want to search. Just change value shown in RED as required.
Updated code should append copied data to next row in sheet 2.

Dave
 
Upvote 0
iris1007 said:
Hi,
I have a huge data which I need to search for Vendor with "delete" note, delete the whole row from the current worksheet and paste the whole row data into a new worksheet.
Click to expand...

So you have a huge data .
How many data rows is it? more than 100k?
Another option is to use filter:
1. Filter the data using criteria contain "delete"
2. then copy the filtered data to the second sheet.
3. back to the first sheet, to delete the filtered data you can use this code:

Code: 
Sub a1079048a()

Dim rng As Range
Set rng = Range("A2", Cells(Rows.count, "A").End(xlUp))
rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete

End Sub
 
Upvote 0
iris1007 said:
Hi Dave,
Awesome. Thank you so much for all the helps. This will really improve my productivity at work.
Click to expand...

Hi,
you are welcome - glad solution does what you want.

Many thanks for feedback

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
VBA - Comparing data and add remarks accordingly
Replies
5
Views
603
oldbrewer
O
H
  • Question Question
Macros - Auto Copy paste row when cell value changes
Replies
7
Views
6K
Fluff
Fluff
N
  • Question Question
excel macro to split data into multiple worksheets based on conditions worksheet
Replies
4
Views
4K
Fluff
Fluff
L
  • Question Question
VBA - Consolidate data and Sum mulitiple ranges
Replies
1
Views
2K
MickG
M
N
  • Question Question
VBA Code to delete empty columns after a certain row
Replies
4
Views
557
NSegna
N

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top