select a list, then sort #2

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sutibusan - 07 May 2008 18:47 GMT

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

Signature

sutibusan

Reply to this Message

Joel - 07 May 2008 19:19 GMT

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
[quoted text clipped - 12 lines]
> end of the worksheet) in column J.
> sutibusan

Reply to this Message

sutibusan - 08 May 2008 18:45 GMT

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.

Signature

sutibusan

> Sub SortJtop()
> Dim Blank As Double
[quoted text clipped - 21 lines]
> > end of the worksheet) in column J.
> > sutibusan

Reply to this Message

Joel - 08 May 2008 18:51 GMT

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
[quoted text clipped - 28 lines]
> > > end of the worksheet) in column J.
> > > sutibusan

Reply to this Message

sutibusan - 09 May 2008 20:34 GMT

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.

Signature

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.
[quoted text clipped - 41 lines]
> > > > end of the worksheet) in column J.
> > > > sutibusan

Reply to this Message

Joel - 09 May 2008 20:58 GMT

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
[quoted text clipped - 54 lines]
> > > > > end of the worksheet) in column J.
> > > > > sutibusan

Reply to this Message

sutibusan - 12 May 2008 18:09 GMT

In my "general" form it is blank rows.

Signature

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?
[quoted text clipped - 67 lines]
> > > > > > end of the worksheet) in column J.
> > > > > > sutibusan

Reply to this Message

Joel - 12 May 2008 22:20 GMT

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.
>
[quoted text clipped - 69 lines]
> > > > > > > end of the worksheet) in column J.
> > > > > > > sutibusan

Reply to this Message

sutibusan - 13 May 2008 19:03 GMT

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.

Signature

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.
[quoted text clipped - 84 lines]
> > > > > > > > end of the worksheet) in column J.
> > > > > > > > sutibusan

Reply to this Message

Joel - 14 May 2008 03:14 GMT

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
[quoted text clipped - 88 lines]
> > > > > > > > > end of the worksheet) in column J.
> > > > > > > > > sutibusan

Reply to this Message

sutibusan - 14 May 2008 18:42 GMT

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

Signature

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
[quoted text clipped - 103 lines]
> > > > > > > > > > end of the worksheet) in column J.
> > > > > > > > > > sutibusan

Reply to this Message

Joel - 14 May 2008 22:19 GMT

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.
[quoted text clipped - 118 lines]
> > > > > > > > > > > end of the worksheet) in column J.
> > > > > > > > > > > sutibusan

Reply to this Message

sutibusan - 15 May 2008 18:52 GMT

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

Signature

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
[quoted text clipped - 127 lines]
> > > > > > > > > > > > end of the worksheet) in column J.
> > > > > > > > > > > > sutibusan

Reply to this Message

Rate this thread: