I have a spreadsheet that has approximately 200 store
numbers in Column A, starting in Row 2. In Column B,
there is either a "1" or a "0" for each store. I'd like
my macro to sort by Column B and then delete all of the
stores that have a "0". There will always be the same
number of stores, but each week their Col B value will
change. I know how to do the sort, but am not sure how to
identify and delete the "0" stores. Any help would be
appreciated. FYI, I'm using Excel 97 in Windows 2000
Professional. Thanks!

Matt

why not use Data | Filter | AutoFilter, select the rows with 0 in column B
and then delete them. No macros required.

Regards

Trevor


"Matt" wrote in message
...

I have a spreadsheet that has approximately 200 store
numbers in Column A, starting in Row 2. In Column B,
there is either a "1" or a "0" for each store. I'd like
my macro to sort by Column B and then delete all of the
stores that have a "0". There will always be the same
number of stores, but each week their Col B value will
change. I know how to do the sort, but am not sure how to
identify and delete the "0" stores. Any help would be
appreciated. FYI, I'm using Excel 97 in Windows 2000
Professional. Thanks!

Try this

It will look from row 2 till the last row with data in column B

Sub test()
Dim r As Long
With Worksheets("Sheet1")
For r = .Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
If .Cells(r, "B").Value = 0 Then
.Rows(r).Delete
End If
Next
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Matt" wrote in message ...

I have a spreadsheet that has approximately 200 store
numbers in Column A, starting in Row 2. In Column B,
there is either a "1" or a "0" for each store. I'd like
my macro to sort by Column B and then delete all of the
stores that have a "0". There will always be the same
number of stores, but each week their Col B value will
change. I know how to do the sort, but am not sure how to
identify and delete the "0" stores. Any help would be
appreciated. FYI, I'm using Excel 97 in Windows 2000
Professional. Thanks!

Faster version

Sub test2()
Dim r As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With Worksheets("Sheet1")
For r = .Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
If .Cells(r, "B").Value = 0 Then
.Rows(r).Delete
End If
Next
End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Matt" wrote in message ...

I have a spreadsheet that has approximately 200 store
numbers in Column A, starting in Row 2. In Column B,
there is either a "1" or a "0" for each store. I'd like
my macro to sort by Column B and then delete all of the
stores that have a "0". There will always be the same
number of stores, but each week their Col B value will
change. I know how to do the sort, but am not sure how to
identify and delete the "0" stores. Any help would be
appreciated. FYI, I'm using Excel 97 in Windows 2000
Professional. Thanks!