delete rows where value of cell "starts with" something

[tahrah]

How would I delete all rows where the value of a cell "starts with"
something. For example, if I have product numbers like 01-31-030,
01-30-132, 01-30-265 etc... and I want to delete all rows that "start
with" 01-30, how would I do that?

Then, if I want to copy ALL remaining rows/columns to a new
spreadsheet, how would I do that?

I appreciate your help.

Regards,
Tahrah

 

[Guest]

Hi Tahrah!

Try this one delete rows, I think it works.

Sub DeleteRows()
Dim selectRow As Long
Dim selectRange As Range
On Error GoTo TheEnd

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set selectRange = ActiveSheet.UsedRange.Rows

For selectRow = selectRange.Rows.Count To 1 Step -1
Cells.Find(What:="01-13-", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows).Activate
selectRange.Rows(selectRow).EntireRow.Delete
Next selectRow
TheEnd:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Regards,
Kari J Keinonen

 

[Guest]

I'm sorry, it's not work.

 

[Guest]

Here you are a little bit better version, sorry.

Public Sub DeleteRows()
Dim selectRow As Long
Dim selectRange As Range
On Error GoTo TheEnd

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set selectRange = ActiveSheet.UsedRange.Rows

For selectRow = selectRange.Rows.Count To 1 Step -1
Cells.Find(What:="01-30-", After:=ActiveCell, LookIn:=xlValues,
LookAt _
:=xlPart, SearchOrder:=xlByRows).Activate
ActiveCell.EntireRow.Delete
Next selectRow
TheEnd:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Regards,
Kari J Keinonen

 

[Ron de Bruin]

Hi tahrah

Install EasyFilter for this (also a option to copy to new sheet)
http://www.rondebruin.nl/easyfilter.htm

Or do a manual autofilter (custom>Start with)

 

[Guest]

I used Worksheets(1) and Worksheets(2) and assumed that the product numbers
are in column A. Try this on a copy of your worksheet before you install it
for permanent use.

Sub deleRwCpy()
Dim myRng As Range
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
Set myRng = Range("A1:A" & lr)
Do
Range("$A$1").Activate
Do
If Left(ActiveCell.Value, 5) <> "01-31" Then
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.EntireRow.Delete
End If
Loop Until ActiveCell.Row >= lr
Loop Until ActiveCell.Row >= lr
Range(Cells(1, 1), Cells(lr, lc)).Copy
Destination:=Worksheets(2).Range("$A$1")
End Sub

 

[tahrah]

JLGWhiz, It's giving a compile error and this line is red on the
macro:
Destination:=Worksheets(2).Range("$A$1")

Any ideas?

Regards,
Tahrah

 

[Dave Peterson]

You got hit by line wrap...

This is one logical line:

Range(Cells(1, 1), Cells(lr, lc)).Copy _
Destination:=Worksheets(2).Range("$A$1")

(Added an underscore followed by a space after the .copy)