Programed sort generates error: originated as recorded macro
 

Hello Excel Gurus,
I have the function command to perform a sort:
Private Function SortRows()
Dim i As Integer
i = 1
With Worksheets("Sheet2")
For i = 1 To 20
Debug.Print .Range("A" & i).Value
If IsEmpty(.Range("A" & i).Value) = True Then
Do While IsEmpty(.Range("A" & i).Value) = True
.Rows(i & ":" & i).Delete Shift:=xlUp
Loop
.Range("A20").Value = "END"
End If
Next i
i = 1
Do While .Range("A" & i).Value < "END"
i = i + 1
Loop
i = i
.Range("A1:J" & i - 1).Select
..Range("A1:J" & i - 1).Sort Key1:=Range("A1"), Order1:=xlAscending,
Key2:=Range("B1"), Order2:=xlAscending
End With
End Function

When it hits the line

..Range("A1:J" & i - 1).Sort Key1:=Range("A1"), Order1:=xlAscending,
Key2:=Range("B1"), Order2:=xlAscending

I get the error #1004:
"The sort reference is not valid. Make sure it's within the data you want to
sort, and the first Sort By box isn't the same or blank."

The command is a simplified version of a recorded macro that generated the same
error. I think the problem may have to do with the fact that I have been
deleting rows prior the issuance of the command. Is that correct? If so how do I
fix it?
Thank You,
-plh


--
Where are we going and why am I in this HAND BASKET??

Programed sort generates error: originated as recorded macro
 

You qualified lots of your ranges, but missed them in this line:

.Range("A1:J" & i - 1).Sort _
Key1:=Range("A1"), Order1:=xlAscending, _
Key2:=Range("B1"), Order2:=xlAscending

.Range("A1:J" & i - 1).Sort _
Key1:=.Range("A1"), Order1:=xlAscending, _
Key2:=.Range("B1"), Order2:=xlAscending

(those dots in the key# parms are important)

plh wrote:

Hello Excel Gurus,
I have the function command to perform a sort:
Private Function SortRows()
Dim i As Integer
i = 1
With Worksheets("Sheet2")
For i = 1 To 20
Debug.Print .Range("A" & i).Value
If IsEmpty(.Range("A" & i).Value) = True Then
Do While IsEmpty(.Range("A" & i).Value) = True
.Rows(i & ":" & i).Delete Shift:=xlUp
Loop
.Range("A20").Value = "END"
End If
Next i
i = 1
Do While .Range("A" & i).Value < "END"
i = i + 1
Loop
i = i
.Range("A1:J" & i - 1).Select
.Range("A1:J" & i - 1).Sort Key1:=Range("A1"), Order1:=xlAscending,
Key2:=Range("B1"), Order2:=xlAscending
End With
End Function

When it hits the line

.Range("A1:J" & i - 1).Sort Key1:=Range("A1"), Order1:=xlAscending,
Key2:=Range("B1"), Order2:=xlAscending

I get the error #1004:
"The sort reference is not valid. Make sure it's within the data you want to
sort, and the first Sort By box isn't the same or blank."

The command is a simplified version of a recorded macro that generated the same
error. I think the problem may have to do with the fact that I have been
deleting rows prior the issuance of the command. Is that correct? If so how do I
fix it?
Thank You,
-plh

--
Where are we going and why am I in this HAND BASKET??

--

Dave Peterson

Programed sort generates error: originated as recorded macro
 

Works swimmingly now, thank you!
-plh

In article , Dave Peterson says...

You qualified lots of your ranges, but missed them in this line:

.Range("A1:J" & i - 1).Sort _
Key1:=Range("A1"), Order1:=xlAscending, _
Key2:=Range("B1"), Order2:=xlAscending

.Range("A1:J" & i - 1).Sort _
Key1:=.Range("A1"), Order1:=xlAscending, _
Key2:=.Range("B1"), Order2:=xlAscending

(those dots in the key# parms are important)

plh wrote:

Hello Excel Gurus,
I have the function command to perform a sort:
Private Function SortRows()
Dim i As Integer
i = 1
With Worksheets("Sheet2")
For i = 1 To 20
Debug.Print .Range("A" & i).Value
If IsEmpty(.Range("A" & i).Value) = True Then
Do While IsEmpty(.Range("A" & i).Value) = True
.Rows(i & ":" & i).Delete Shift:=xlUp
Loop
.Range("A20").Value = "END"
End If
Next i
i = 1
Do While .Range("A" & i).Value < "END"
i = i + 1
Loop
i = i
.Range("A1:J" & i - 1).Select
.Range("A1:J" & i - 1).Sort Key1:=Range("A1"), Order1:=xlAscending,
Key2:=Range("B1"), Order2:=xlAscending
End With
End Function

When it hits the line

.Range("A1:J" & i - 1).Sort Key1:=Range("A1"), Order1:=xlAscending,
Key2:=Range("B1"), Order2:=xlAscending

I get the error #1004:
"The sort reference is not valid. Make sure it's within the data you want to
sort, and the first Sort By box isn't the same or blank."

The command is a simplified version of a recorded macro that generated the same
error. I think the problem may have to do with the fact that I have been
deleting rows prior the issuance of the command. Is that correct? If so how do I
fix it?
Thank You,
-plh

--
Where are we going and why am I in this HAND BASKET??



--
Where are we going and why am I in this HAND BASKET??