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

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


--
__________________________________
HTH

Bob

"Forgone" <(E-Mail Removed)> wrote in message
news:8eabf1a0-ec06-4298-8c34-(E-Mail Removed)...
> 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......

Hi Bob,

Thanks, worked a charm!


On Sep 24, 4:06*pm, "Bob Phillips" <Bob...@somewhere.com> wrote:
> 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
>
> --
> __________________________________
> HTH
>
> Bob
>