delete unused rows

[Forgone]

Hi everyone,

Can someone tell me what is wrong with this syntax? To me it looks
fine but it doesn't delete the row within the selection it deleted
everything in the spreadsheet.


What I have done is:

* a range of cells (G19:G36) is named as range1
* another range (Xn:Xn) is named as rangen

towards the end of the sub it selects range1 with the following
code....

Range("range1").Select
Call delete_unusedrows

the syntax for delete_unusedrows is

-----------------------------------------------------------------------------------------------------
Sub delete_unusedrows()
On Error Resume Next

For Each cell In Selection

If cell.Offset(0, -6) = "R" Or cell.Offset(0, -6) = "E" Then
If cell = 0 Then
Cells.Rows.EntireRow.Delete = True
End If
End If

Next cell

End Sub
-----------------------------------------------------------------------------------------------------

One of the problems that I came across is that I am selecting the
"cell" where I'm verifying that the value = 0. I did a macro to test
if the cell = 0 worked and it appeared to have worked.

Thus.........

Why is it that when it gets to range1 it deletes everything rather
than what is in that selection?

I'm stumped......

 

[Bob Phillips]

Sub delete_unusedrows()
Dim i As Long

With Selection

For i = .Rows.Count + .Cells(1, 1).Row - 1 To .Cells(1, 1).Row
Step -1

If Cells(i, .Column - 6).Value = "R" Or Cells(i, .Column -
6).Value = "E" Then

If Cells(i, .Column) = 0 Then

Rows(i).Delete
End If
End If
Next i
End With
End Sub

 

[Forgone]

Hi Bob,

Thanks, worked a charm!