Deleting rows where value in column B starts with specific letter

[radka.silva]

Hello,

first, let me apologize for my questions as I know very little about VBA code and I am just trying make my life less complicated. I have searched through the old posts and even though I have found a lot of information on deleting rows, I can't find enough information about my particular condition.

In my spreadsheet, I need to delete all rows except the once whose account number starts with BS - Column B in my spreadsheet.

I have attached my spreadsheet with the data.

I am using Excel 2010

I appreaciate your help!!!

Radka

[Yongle]

You have zero account numbers in the your file beginning with BS - so delete everything!
You do not need VBA for this. This is easier without VBA

Select Ribbon DATA
Filter
text filter
Custom Filter
"Does not begin with" "BS"

If you must use vba, use RECORD MACRO and record above selections

[radka.silva]

Porfolio Code (Column B) is the account number, sorry, should have specified. Line 48 through 72 has the accounts that I need. I know I can just delete them manually or filter them out, but this one function is a part of a longer sorting/editing macro and I can't proceed with all the other accounts on my sheet. Thanks!!

[Yongle]

Try this - the macro filters to exclude accounts beginning BS and deletes everything, which leaves only accounts beginning BS


Sub Delete_all_except_BS()
'set variables
Dim lastRow As Long
'find last row and last columns
Sheets("sheet1").Select
Range("A1").Select
Selection.End(xlDown).Select
lastRow = ActiveCell.Row
'filter to exclude all accounts starting BS
ActiveSheet.Range("A1:F" & lastRow).AutoFilter Field:=2, Criteria1:="<>BS*", _
Operator:=xlAnd
'delete all the visible rows and this will leave only those beginning BS
Selection.Offset(1).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
End Sub

[apo]

Same but different...



Private Sub CommandButton1_Click()
With Range("A1:J" & Range("A" & Rows.Count).End(xlUp).Row)
.AutoFilter 2, "<>BS*"
ActiveSheet.AutoFilter.Range.Offset(1).Resize(ActiveSheet.AutoFilter.Range. Rows.Count - 1).SpecialCells(12).EntireRow.Delete
.AutoFilter
End With
End Sub

[radka.silva]

Thank you so much, both of these work perfectly!