Delete rows based on dropdown list value

[bala04msw]

Dear Experts,

Need VBA code guidance for the following requirements and attached excel model sheet for your kind understanding:

Screenshot (125).png

If i select the state name in the dropdown list, i want to keep only that states data in the table and rest of the other states data to be deleted and i have used the following code. It works perfectly, but header is deleted as per the below given code. But I want the table with header.

Sub deleterows_droplist_1()
Dim ws As Worksheet
Dim Rng As Range
Dim lastRow As Long

Set ws = ActiveWorkbook.Sheets("Test")
lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
Set Rng = ws.Range("A1:A" & lastRow)
' filter and delete all but header row
With Rng
.AutoFilter Field:=1, Criteria1:="<>" & ws.Range("F1").Value
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
' turn off the filters
ws.AutoFilterMode = False

End Sub

Regards

[LeoTaxi]

Maybe change

Set Rng = ws.Range("A1:A" & lastRow)

to

Set Rng = ws.Range("A2:A" & lastRow)

Kind regards
Leo

[bala04msw]

Dear Mr. Leo,

I have tried that too. If i select A2 as a range, that will delete the existing row data. Thanks for your time and reply. Could you please help me in other way?

Regards

[LeoTaxi]

This can do

Sub Leo()
With Sheets("Test")
    .Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:="<>" & .Range("F1").Value
    .Range("A1").CurrentRegion.Offset(1).EntireRow.Delete
    .AutoFilterMode = False
End With
End Sub

Kind regards
Leo

[bala04msw]

This can do

Sub Leo()
With Sheets("Test")
    .Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:="<>" & .Range("F1").Value
    .Range("A1").CurrentRegion.Offset(1).EntireRow.Delete
    .AutoFilterMode = False
End With
End Sub

Kind regards
Leo

Dear leo,

Thanks a lot and it works perfectly. Great learning today.

Regards