Delete all rows after copying in new sheet by vba

[m_vishal_c]

HI i need to delete all rows after copying in new sheet.

I am using below code

last = Sheets(sht).Cells(Rows.Count, "A").End(xlUp).RowSet rng = Sheets(sht).Range("A1:F" & last)
Set rng1 = Worksheets("Need to be removed 0").Range("B2:B4")


'Sheets(sht).Range("F1:F" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("L1"), Unique:=True ''' THIS IS FOR ENTIRE RANGE NOT FOR PERTICULAR VALUE, Macro will create all values's extra sheet




Dim sCriteria As String




For Each x In rng1 


sCriteria = "*" & x.Value & "*"
With rng
    '.AutoFilter Field:=1, Criteria1:="=(x.value)*", Operator:=xlFilterValues
     .AutoFilter Field:=3, Criteria1:=sCriteria, Operator:=xlFilterValues
    .SpecialCells(xlCellTypeVisible).Copy
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = x.Value
    ActiveSheet.Paste
End With
Next x


' Turn off filter
Sheets(sht).AutoFilterMode = False

Heaps thanks

 

[m_vishal_c]

Look at my first reply. That line of code should be outside the For ... Next loop.
My understanding is that you want to delete the source, rng. If not, please clarify.

hi, i tried the way you told me but it still does not remove / delete rows from sheet1 and it delete header from Sheet1.

Please guide me. if you want i can upload file for you. Please let me know

thanks

 

[yky]

Please upload the file to a site which doesn't need a password for download. Delete all sensitive data before uploading the file.

 

[m_vishal_c]

can you please let me know how can i upload file. thanks

 

[yky]

There are many sites on the Internet that allow you to upload and share files, such as Dropbox. Please pick one that doesn't require a password for download.

 

[m_vishal_c]

hi Please see below link. if does not work then please let me know

https://abcvishalmasharuwala.sharefile.com/d-s05ef3e75ea94c30a

thanks

 

[yky]

So, you want to delete A1:F3883 in Sheet1 and leave L1:L10 intact?

 

[m_vishal_c]

let me explain. you can see HFC Infill, LOCID, Copper etc unique value. i want to move rows related to those value in by creating new sheet and move those value from sheet1. thanks

 

[m_vishal_c]

let me explain. you can see HFC Infill, LOCID, Copper etc unique value. i want to move rows related to those value in by creating new sheet and move those value from sheet1. thanks

in other way you are right
After copying to new sheet, need to remove those rows from sheet1.

 

[yky]

let me explain. you can see HFC Infill, LOCID, Copper etc unique value. i want to move rows related to those value in by creating new sheet and move those value from sheet1. thanks

The command button already does that.

 

[yky]

Try this. See if it is what you want. This code deletes those rows that are copied to other sheets and leave uncopied rows intack.

.............
.............
Sheets(sht).AutoFilterMode = False

[COLOR=#ff0000]rng.EntireRow.Delete[/COLOR]  '<- add this line