I have a presorted Excel worksheet, & I'm trying to sort only the top part of
it from where I end up after the 1st sort (the 1st blank line after the top
part of the worksheet, but in tht middle of it[a daily variable starting
point]). This recorded macro & my attempt to get it under my control, sorts
the whole worksheet & gives me an error 1004 @ Range("J3:J", colA):
Sub SortJtop()
Dim Blank As Double
Blank = ActiveCell ' Grab the row # of the starting (blank) cell
ActiveCell.Sort Key1:=Range("J3:J", colA), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub
To clarify, I need to sort from row 3 to row x (a daily variable #, not the
end of the worksheet) in column J.
sutibusan
--
sutibusan

Sub SortJtop()
Dim Blank As Double
Set SortRange = Range("J3:J" & x)
SortRange.Sort _
Key1:=Range("J3"), _
Order1:=xlAscending, _
Header:= xlYes
End Sub

"sutibusan" wrote:

I have a presorted Excel worksheet, & I'm trying to sort only the top part of
it from where I end up after the 1st sort (the 1st blank line after the top
part of the worksheet, but in tht middle of it[a daily variable starting
point]). This recorded macro & my attempt to get it under my control, sorts
the whole worksheet & gives me an error 1004 @ Range("J3:J", colA):
Sub SortJtop()
Dim Blank As Double
Blank = ActiveCell ' Grab the row # of the starting (blank) cell
ActiveCell.Sort Key1:=Range("J3:J", colA), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub
To clarify, I need to sort from row 3 to row x (a daily variable #, not the
end of the worksheet) in column J.
sutibusan
--
sutibusan

Joel,
It seems to be a problem of catching where the end of my sort range is (my
'x').
I get a 1004 error at 'Set SortRange = Range("J3:J" & x)'. I thought a
'Blank = ActiveCell' & going with 'Set SortRange = Range("J3:J" & Blank)',
would catch my starting/ending point, but that doesn't do it either.
--
sutibusan


"Joel" wrote:


Sub SortJtop()
Dim Blank As Double
Set SortRange = Range("J3:J" & x)
SortRange.Sort _
Key1:=Range("J3"), _
Order1:=xlAscending, _
Header:= xlYes
End Sub

"sutibusan" wrote:

I have a presorted Excel worksheet, & I'm trying to sort only the top part of
it from where I end up after the 1st sort (the 1st blank line after the top
part of the worksheet, but in tht middle of it[a daily variable starting
point]). This recorded macro & my attempt to get it under my control, sorts
the whole worksheet & gives me an error 1004 @ Range("J3:J", colA):
Sub SortJtop()
Dim Blank As Double
Blank = ActiveCell ' Grab the row # of the starting (blank) cell
ActiveCell.Sort Key1:=Range("J3:J", colA), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub
To clarify, I need to sort from row 3 to row x (a daily variable #, not the
end of the worksheet) in column J.
sutibusan
--
sutibusan

The new Line LastRow goes to Column J at Row 65536 (rows.count) and moves up
the column until it finds a cell with data.

Sub SortJtop()
Dim Blank As Double
LastRow = Range("J" & Rows.Count).End(xlup).Row
Set SortRange = Range("J3:J" & LastRow)
SortRange.Sort _
Key1:=Range("J3"), _
Order1:=xlAscending, _
Header:= xlYes
End Sub

"sutibusan" wrote:

Joel,
It seems to be a problem of catching where the end of my sort range is (my
'x').
I get a 1004 error at 'Set SortRange = Range("J3:J" & x)'. I thought a
'Blank = ActiveCell' & going with 'Set SortRange = Range("J3:J" & Blank)',
would catch my starting/ending point, but that doesn't do it either.
--
sutibusan


"Joel" wrote:


Sub SortJtop()
Dim Blank As Double
Set SortRange = Range("J3:J" & x)
SortRange.Sort _
Key1:=Range("J3"), _
Order1:=xlAscending, _
Header:= xlYes
End Sub

"sutibusan" wrote:

I have a presorted Excel worksheet, & I'm trying to sort only the top part of
it from where I end up after the 1st sort (the 1st blank line after the top
part of the worksheet, but in tht middle of it[a daily variable starting
point]). This recorded macro & my attempt to get it under my control, sorts
the whole worksheet & gives me an error 1004 @ Range("J3:J", colA):
Sub SortJtop()
Dim Blank As Double
Blank = ActiveCell ' Grab the row # of the starting (blank) cell
ActiveCell.Sort Key1:=Range("J3:J", colA), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub
To clarify, I need to sort from row 3 to row x (a daily variable #, not the
end of the worksheet) in column J.
sutibusan
--
sutibusan

It will find the data I don't need to sort. My data is in this "general" form:
Data1
Data2
Data3
Zip1 (blank rows)
Data5
Data4
Data6
What I need to sort is Data1 to Data3 (based on column J), because my last
sort left me at Zip1. What I haven't been able to figure our is how to catch
is where Zip1 is, so my macro will sort from row 3 to row(Zip1). The row
where Zip1 is will change daily.
--
sutibusan


"Joel" wrote:

The new Line LastRow goes to Column J at Row 65536 (rows.count) and moves up
the column until it finds a cell with data.

Sub SortJtop()
Dim Blank As Double
LastRow = Range("J" & Rows.Count).End(xlup).Row
Set SortRange = Range("J3:J" & LastRow)
SortRange.Sort _
Key1:=Range("J3"), _
Order1:=xlAscending, _
Header:= xlYes
End Sub

"sutibusan" wrote:

Joel,
It seems to be a problem of catching where the end of my sort range is (my
'x').
I get a 1004 error at 'Set SortRange = Range("J3:J" & x)'. I thought a
'Blank = ActiveCell' & going with 'Set SortRange = Range("J3:J" & Blank)',
would catch my starting/ending point, but that doesn't do it either.
--
sutibusan


"Joel" wrote:


Sub SortJtop()
Dim Blank As Double
Set SortRange = Range("J3:J" & x)
SortRange.Sort _
Key1:=Range("J3"), _
Order1:=xlAscending, _
Header:= xlYes
End Sub

"sutibusan" wrote:

I have a presorted Excel worksheet, & I'm trying to sort only the top part of
it from where I end up after the 1st sort (the 1st blank line after the top
part of the worksheet, but in tht middle of it[a daily variable starting
point]). This recorded macro & my attempt to get it under my control, sorts
the whole worksheet & gives me an error 1004 @ Range("J3:J", colA):
Sub SortJtop()
Dim Blank As Double
Blank = ActiveCell ' Grab the row # of the starting (blank) cell
ActiveCell.Sort Key1:=Range("J3:J", colA), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub
To clarify, I need to sort from row 3 to row x (a daily variable #, not the
end of the worksheet) in column J.
sutibusan
--
sutibusan

Then you need to use xldown instead of xlup. the code search column J for
last row before Blank Row. Is Zip1 certain data or just a blnk Row?

Sub SortJtop()
Dim Blank As Double
LastRow = Range("J1").End(xldown).Row
Set SortRange = Range("J3:J" & LastRow)
SortRange.Sort _
Key1:=Range("J3"), _
Order1:=xlAscending, _
Header:= xlYes
End Sub


"sutibusan" wrote:

It will find the data I don't need to sort. My data is in this "general" form:
Data1
Data2
Data3
Zip1 (blank rows)
Data5
Data4
Data6
What I need to sort is Data1 to Data3 (based on column J), because my last
sort left me at Zip1. What I haven't been able to figure our is how to catch
is where Zip1 is, so my macro will sort from row 3 to row(Zip1). The row
where Zip1 is will change daily.
--
sutibusan


"Joel" wrote:

The new Line LastRow goes to Column J at Row 65536 (rows.count) and moves up
the column until it finds a cell with data.

Sub SortJtop()
Dim Blank As Double
LastRow = Range("J" & Rows.Count).End(xlup).Row
Set SortRange = Range("J3:J" & LastRow)
SortRange.Sort _
Key1:=Range("J3"), _
Order1:=xlAscending, _
Header:= xlYes
End Sub

"sutibusan" wrote:

Joel,
It seems to be a problem of catching where the end of my sort range is (my
'x').
I get a 1004 error at 'Set SortRange = Range("J3:J" & x)'. I thought a
'Blank = ActiveCell' & going with 'Set SortRange = Range("J3:J" & Blank)',
would catch my starting/ending point, but that doesn't do it either.
--
sutibusan


"Joel" wrote:


Sub SortJtop()
Dim Blank As Double
Set SortRange = Range("J3:J" & x)
SortRange.Sort _
Key1:=Range("J3"), _
Order1:=xlAscending, _
Header:= xlYes
End Sub

"sutibusan" wrote:

I have a presorted Excel worksheet, & I'm trying to sort only the top part of
it from where I end up after the 1st sort (the 1st blank line after the top
part of the worksheet, but in tht middle of it[a daily variable starting
point]). This recorded macro & my attempt to get it under my control, sorts
the whole worksheet & gives me an error 1004 @ Range("J3:J", colA):
Sub SortJtop()
Dim Blank As Double
Blank = ActiveCell ' Grab the row # of the starting (blank) cell
ActiveCell.Sort Key1:=Range("J3:J", colA), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub
To clarify, I need to sort from row 3 to row x (a daily variable #, not the
end of the worksheet) in column J.
sutibusan
--
sutibusan