select a list, then sort #2

[sutibusan]

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

 

[Joel]

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

 

[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

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

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

 

[Joel]

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

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

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

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

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

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

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

:

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

 

[Joel]

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

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

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

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


:


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

:

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]

In my "general" form it is blank rows.
--
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

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

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

:

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


:


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

:

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

 

[Joel]

The code should work as is if the sheet is the active sheet. If you want to
improve the code then include the sheet name as shown below.

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

In my "general" form it is blank rows.
--
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

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


:

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

:

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


:


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

:

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]

I have rows 1 & 2 as headers & row 2 gets pasted to row 62.
If I change J1 in '.Range ("J1")' to J3, the data in J5 moves to J4 (it was
blank) & I'm not getting my sort by rows.
--
sutibusan

The code should work as is if the sheet is the active sheet. If you want to
improve the code then include the sheet name as shown below.

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

In my "general" form it is blank rows.
--
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


:

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


:

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

:

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


:


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

:

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

 

[Joel]

With a sort the header row is only one row. You need to make the header
option xlNo and start the sort at row 3

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

I have rows 1 & 2 as headers & row 2 gets pasted to row 62.
If I change J1 in '.Range ("J1")' to J3, the data in J5 moves to J4 (it was
blank) & I'm not getting my sort by rows.
--
sutibusan

The code should work as is if the sheet is the active sheet. If you want to
improve the code then include the sheet name as shown below.

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

In my "general" form it is blank rows.
--
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


:

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


:

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

:

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


:


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

:

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]

I get the sort done right, except the original row 1 & 2 are now between the
rows that have data in column J & those that have no data in column J.
Heres's my sub:
Sub SortJtop()
'Dim Blank As Double' I don't need this since Blank is not used anywhere
With Sheets("EXPIDITE.RPT")
Lastrow = .Range("J1").End(xlDown).Row
Set SortRange = .Range("J3:J" & Lastrow)
SortRange.Sort _
Key1:=.Range("J3"), _
Order1:=xlAscending, _
Header:=xlNo
End With
End Sub
--
sutibusan

With a sort the header row is only one row. You need to make the header
option xlNo and start the sort at row 3

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

I have rows 1 & 2 as headers & row 2 gets pasted to row 62.
If I change J1 in '.Range ("J1")' to J3, the data in J5 moves to J4 (it was
blank) & I'm not getting my sort by rows.
--
sutibusan

The code should work as is if the sheet is the active sheet. If you want to
improve the code then include the sheet name as shown below.

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


:

In my "general" form it is blank rows.
--
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


:

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


:

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

:

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


:


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

:

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

 

[Joel]

what you are saying doesn't make sense unless the old code moved the two
header rows. try manually putting the headers back in there original
locations and try the code again. Also you can try the following

from
Lastrow = .Range("J1").End(xlDown).Row
to
Lastrow = .Range("J3").End(xlDown).Row

I get the sort done right, except the original row 1 & 2 are now between the
rows that have data in column J & those that have no data in column J.
Heres's my sub:
Sub SortJtop()
'Dim Blank As Double' I don't need this since Blank is not used anywhere
With Sheets("EXPIDITE.RPT")
Lastrow = .Range("J1").End(xlDown).Row
Set SortRange = .Range("J3:J" & Lastrow)
SortRange.Sort _
Key1:=.Range("J3"), _
Order1:=xlAscending, _
Header:=xlNo
End With
End Sub
--
sutibusan

With a sort the header row is only one row. You need to make the header
option xlNo and start the sort at row 3

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

I have rows 1 & 2 as headers & row 2 gets pasted to row 62.
If I change J1 in '.Range ("J1")' to J3, the data in J5 moves to J4 (it was
blank) & I'm not getting my sort by rows.
--
sutibusan


:

The code should work as is if the sheet is the active sheet. If you want to
improve the code then include the sheet name as shown below.

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


:

In my "general" form it is blank rows.
--
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


:

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


:

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

:

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


:


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

:

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]

I reload the file to be sorted each time before running the macro. I use the
version you've given me each time. With J1 in the Lastrow function, it sorts
the top 2 rows into the middle of the data, but with J3 in the function, it
only moves the data from J5 to J4. At least, that's all I see change.
I've been expecting your code to do the trick for me, but, I just tried a
different way by recording another macro: move the 2 header rows to Sheet1,
go back to the main worksheet, run the sort you gave me & it does the trick
(with J1 in the Lastrow function). Since I moved the header rows one way,
it'll be a snap to move them back.
It's stuff like this that really makes me want to slap Bill Gates!
Thank you for your help, Joel
--
sutibusan

what you are saying doesn't make sense unless the old code moved the two
header rows. try manually putting the headers back in there original
locations and try the code again. Also you can try the following

from
Lastrow = .Range("J1").End(xlDown).Row
to
Lastrow = .Range("J3").End(xlDown).Row

I get the sort done right, except the original row 1 & 2 are now between the
rows that have data in column J & those that have no data in column J.
Heres's my sub:
Sub SortJtop()
'Dim Blank As Double' I don't need this since Blank is not used anywhere
With Sheets("EXPIDITE.RPT")
Lastrow = .Range("J1").End(xlDown).Row
Set SortRange = .Range("J3:J" & Lastrow)
SortRange.Sort _
Key1:=.Range("J3"), _
Order1:=xlAscending, _
Header:=xlNo
End With
End Sub
--
sutibusan

With a sort the header row is only one row. You need to make the header
option xlNo and start the sort at row 3

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


:

I have rows 1 & 2 as headers & row 2 gets pasted to row 62.
If I change J1 in '.Range ("J1")' to J3, the data in J5 moves to J4 (it was
blank) & I'm not getting my sort by rows.
--
sutibusan


:

The code should work as is if the sheet is the active sheet. If you want to
improve the code then include the sheet name as shown below.

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


:

In my "general" form it is blank rows.
--
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


:

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


:

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

:

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


:


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

:

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