I changed the variable name from i to iRow when I was almost done.

This line:
'iRow'd include all the parms for the .find command.
used to read:
'I'd include all the parms for the .find command.

(there may be more <vbg.)

Dave Peterson wrote:

If you're using that with/end with structure, then you'll want to qualify all
those range objects (.range() and .cells()).

If Left(.Cells(i, 1), 5).Value = "Lname" Then
.Range(.Cells(i, 1), .Cells(i, 5)).ClearContents

And since I didn't see where you used that with/end with, I'd add it.

Option Explicit
Sub DelRows()

'this declares iRow as a variant and LastRow as long:
'Dim i, n As Long
'you could use:
'Dim i as long, n as long
'but I like separate lines:

'I changed the variable names, too
Dim iRow As Long
Dim LastRow As Long

Dim wsNumFrum As Worksheet

Set wsNumFrum = Worksheets("Numbers from Reverse Directory")

With wsNumFrum
'iRow'd include all the parms for the .find command.
'if you don't, then you'll be getting the parms from the last
'.find -- either by the user or by some other code.
LastRow = .Cells.Find(What:="*", _
After:=.Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

For iRow = 1 To LastRow
If .Cells(iRow, 1).Value = "" Then
.Range(.Cells(iRow, 1), .Cells(iRow, 5)).ClearContents
Else
If LCase(Left(.Cells(iRow, 1).Value, 5)) = LCase("Lname") Then
.Range(.Cells(iRow, 1), .Cells(iRow, 5)).ClearContents
Else
If IsNumeric(.Cells(iRow, 5).Value) Then
.Range(.Cells(iRow, 1), .Cells(iRow, 5)).ClearContents
End If
End If
End If
Next iRow

'Since the code is in the previous with statement, it's
'kind of equivalent to:
'with wsNumFrum.sort
With .Sort
.SortFields.Clear
.SortFields.Add _
Key:=Parent.Range("A1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange .Parent.Range("A1:E5001")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End Sub

======
And I think that this portion:

For iRow = 1 To LastRow
If .Cells(iRow, 1).Value = "" Then
.Range(.Cells(iRow, 1), .Cells(iRow, 5)).ClearContents
Else
If LCase(Left(.Cells(iRow, 1).Value, 5)) = LCase("Lname") Then
.Range(.Cells(iRow, 1), .Cells(iRow, 5)).ClearContents
Else
If IsNumeric(.Cells(iRow, 5).Value) Then
.Range(.Cells(iRow, 1), .Cells(iRow, 5)).ClearContents
End If
End If
End If
Next iRow

can use some if/or logic:

For iRow = 1 To LastRow
If .Cells(iRow, 1).Value = "" _
or LCase(Left(.Cells(iRow, 1).Value, 5)) = LCase("Lname") _
or IsNumeric(.Cells(iRow, 5).Value) Then
.Range(.Cells(iRow, 1), .Cells(iRow, 5)).ClearContents
end if
next irow

(Untested, uncompiled. Watch for typos.)

Jim Berglund wrote:

OK, Thanks for the explanation. I am down to the short strokes and now have
only one hiccup left (it may have been missed in a previous note)

If Left(.Cells(i, 1), 5).Value = "Lname" Then
.Range(Cells(i, 1), Cells(i, 5)).ClearContents

What's wrong here, please?

Jim

"Dave Peterson" wrote in message
...
This kind of syntax is pretty common:

Range(Cells(i, 1), Cells(i, 5)).ClearContents

But I bet that this syntax isn't doing what you want:
If Range(Cells(i, 1)).Value = "" Then

I bet you meant:
If Cells(i, 1).Value = "" Then

If you use:
If Range(Cells(i, 1)).Value = "" Then

Then cells(i,1) better contain something that looks like an address (or a
range
name). Otherwise, you'll get a different error.

Since the default property of that cells(i,1) is value, you're doing:
If Range(Cells(i, 1).value).Value = "" Then

It makes sense if Cells(i,1) contained something like: b99
Then your code would be doing:
if range("B99").value = "" then





Jim Berglund wrote:

I have been trying to run the following code, and am getting a Next
without
For Error
Any Ideas?

Jim Berglund

Sub DelRows()

Dim i, n As Long
Dim wsNumFrum As Worksheet

Set wsNumFrum = Worksheets("Numbers from Reverse Directory")
n = wsNumFrum.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row
For i = 1 To n
If Range(Cells(i, 1)).Value = "" Then
Range(Cells(i, 1), Cells(i, 5)).ClearContents
Else
Range(Cells(i, 1)).Select
If Left(Cells(i, 1), 5).Value = "Lname" Then
Range(Cells(i, 1), Cells(i, 5)).ClearContents
Else
If IsNumeric(Cells(i, 5).Value) Then
Range(Cells(i, 1), Cells(i, 5)).ClearContents
End If
Next
'Sort the data
ActiveWorkbook.Worksheets("Numbers from Reverse
Directory").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Numbers from Reverse
Directory").Sort.SortFields. _
Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending,
_
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Numbers from Reverse Directory").Sort
.SetRange Range("A1:E5001")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub


--

Dave Peterson

--

Dave Peterson

--

Dave Peterson