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

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

I'm sorry, it's not work.

"Kari J Keinonen" wrote:

> 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

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

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)


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"tahrah" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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
>

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" wrote:

> 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
>
>

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


JLGWhiz wrote:
> 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" wrote:
>
> > 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
> >
> >

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)

tahrah wrote:
>
> 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
>
> JLGWhiz wrote:
> > 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" wrote:
> >
> > > 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
> > >
> > >

--

Dave Peterson