How to delete visible cells from range without deleting headers

[ThrottleWorks]

Hi,

I am trying to delete visible cells from a filtered range.
As of now I am able to do it however I am not sure where it is the correct way.

Is there a single line code, which will help me to delete visible cells from the range but will not delete headers.

At present I am using "Range(TempSht1.Cells(2, 1), TempSht1.Cells(TempLr, TempCol)).SpecialCells(xlCellTypeVisible).EntireRow.Delete" this line to avoid deleting header.

Can anyone please help me in this.

Set TempRng = TempSht1.Range(TempSht1.Cells(1, 1), TempSht1.Cells(TempLr, TempCol))
    On Error Resume Next
        Selection.AutoFilter
            TempRng.AutoFilter Field:=Col, Criteria1:="=Honda", Operator:=xlAnd
            TempLr1 = TempSht1.Cells(Rows.Count, Col).End(xlUp).Row
           
            If TempLr1 > 1 Then
                Range(TempSht1.Cells(2, 1), TempSht1.Cells(TempLr, TempCol)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            End If
       
        Selection.AutoFilter
    On Error GoTo 0

Set TempRng = TempSht1.Range(TempSht1.Cells(1, 1), TempSht1.Cells(TempLr, TempCol))
On Error Resume Next
Selection.AutoFilter
TempRng.AutoFilter Field:=Col, Criteria1:="=Honda", Operator:=xlAnd
TempLr1 = TempSht1.Cells(Rows.Count, Col).End(xlUp).Row
If TempLr1 > 1 Then
Range(TempSht1.Cells(2, 1), TempSht1.Cells(TempLr, TempCol)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End If
Selection.AutoFilter
On Error GoTo 0

 

[Ratan Bhushan]

Please upload sample file

 

[ThrottleWorks]

Hi @Ratan Bhushan , thanks for the help, sorry will not be able to upload sample file.

 

[zohaib]

Hello,

Try this:

ActiveSheet.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Select
   
Selection.Delete Shift:=xlUp

 

[Marc L]

Hi !

No need to select neither SpecialCells
as Copy like Delete methods use only visible cells !
From source range with headers row, just need an Offset.

With a dummy sample workbook, I could post a demonstration …
​

 

[ThrottleWorks]

Hi @zohaib and @Marc L thanks a lot for the help. Have a nice day ahead.