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]

The command button already does that.

Yes, you are right but it does not delete rows from Sheet1 after copying to new sheet. I want to delete rows from Sheet1 after copying those values to another sheet.

 

[Fluff]

Is this what you want

Sheets(sht).AutoFilterMode = False
[COLOR=#ff0000]rng.Offset(1).EntireRow.Delete[/COLOR]

 

[m_vishal_c]

Is this what you want

Sheets(sht).AutoFilterMode = False
[COLOR=#ff0000]rng.Offset(1).EntireRow.Delete[/COLOR]

Yes, thanks a lot for your time and support. Much appreciate

 

[Fluff]

Glad we could help & thanks for the feedback