Find last cell and sort

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
538
Hello,
I’m sorting column F in descending order to push all cells with a valid date to the top of the column. I have F11-F14 hidden (conditional format) since a date does not exist yet. Here is the sort code

Code:
Sub Ndateq1()
Sheets("Northern").Select
    Selection.Sort Key1:=Range("F1"), Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
End Sub

Is it possible to add if statements during the sort to exclude cells with the date 1/0/00 so I can sort in ascending order?

Then, I want to run another sort using the range A1:F10 automatically (sorting columns, A, B & C in ascending order). The range would be defined A1 to the last used cell (with a valid date) in column F. The ending cell of column F would change as more dates are added. I tried using MATCH (9.99999999999999E+307, F:F)-CELL("Row",F1)+1 to identify the last used cell in column F but it includes the cells that are hidden with the date format 1/0/00.

Any help is appreciated.

Here is a sample of the worksheet.
Copy of Master Audit Summary Tracker.xls
ABCDEF
1NYMETRO1267FARMINGDALE(RELO)FARMINGDALENY7/26/06
2CENTRAL1969MCHENRYMCHENRYIL7/26/06
3NYMETRO6217WALLINGFORDWALLINGFORDCT7/21/06
4CENTRAL1920CRYSTALLAKECRYSTALLAKEIL7/19/06
5OHIOVALLEY3814CROSSCOUNTY(COLERAIN)CINCINNATIOH7/19/06
6OHIOVALLEY3832PLEASANTRIDGECINCINNATIOH7/12/06
7NJMETRO974MANTUAMANTUANJ5/17/06
8OHIOVALLEY3817STRONGSVILLESTRONGSVILLEOH5/17/06
9CENTRAL2030VALPARAISOVALPARAISOIN5/11/06
10NJMETRO1286HAMBURGHAMBURGNY5/10/06
11NYMETRO901EHANOVEREASTHANOVERNJ1/0/00
12NJMETRO902LAKEWOODLAKEWOODNJ1/0/00
13NJMETRO903SPLAINFIELDSOUTHPLAINFIELDNJ1/0/00
14NYMETRO904PARAMUSPARAMUSNJ1/0/00
Northern


F11=IF(ISERROR(INDEX(Master!$J$2:$J$2048,MATCH(B11,Master!$D$2:$D$2048,0)))," ",INDEX(Master!$J$2:$J$2048,MATCH(B11,Master!$D$2:$D$2048,0)))
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Doug.T

Board Regular
Joined
Sep 19, 2006
Messages
80
Part 1:
You could replace the 1/1/00 dates (is that 1/1/1900?) with a higher date (ie year 2200), sort, then change them back to 1/1/00.

Columns("K:K").Replace What:="1/1/1900", Replacement:="1/1/2200", LookAt:= xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

'sort macro lines

Columns("K:K").Replace What:="1/1/2200", Replacement:="1/1/1900", LookAt:= xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
 

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
538
Thank you for your reply

The find and replace didn’t pick up the dates 1/0/00. I believe it’s because they’re hidden and the result of an empty cell. The index and match are pulling the dates from a column of a master sheet that is blank unless a date has been manually entered.
 

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
538
I figured out a work around the ascending issue with the date column. I put a hidden column with the date unformatted and added a formula =IF(I1>0,I1,"") in order to hide the formula. Then, I sort by the hidden column.

Now, I want to create 2 sorts. One to sort the dates, pushing them up to the top of the spread sheet, then sort by column A using the data up to the last entry in column I, in one step.

For example;

Rich (BB code):
Sub test()
'

'
    Range("A1:L631").Sort Key1:=Range("H1"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A1:I8").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("M16").Select
End Sub
Before the sort:
Copy of Master Audit Summary Tracker.xls
ABCDEFGHI
1NJMETRO1286HAMBURGHAMBURGNY 388475/10/06
2CENTRAL2030VALPARAISOVALPARAISOINyes388485/11/06
3NJMETRO974MANTUAMANTUANJ 388545/17/06
4OHIOVALLEY3817STRONGSVILLESTRONGSVILLEOH 388545/17/06
5NJMETRO942WASHINGTONTWSP/MONROESICKLERVILLENJ 388555/18/06
6NJMETRO1244GREECEGREECENY 388605/23/06
7OHIOVALLEY2703CANTONCANTONMI 388605/23/06
8NJMETRO1268LOCKPORTLOCKPORTNY 388615/24/06
9CENTRAL198647THANDWESTERNCHICAGOILyes 1/0/00
10NJMETRO975ABSECONABSECONNJ  1/0/00
11NJMETRO1241ALBANYALBANYNY  1/0/00
12NJMETRO1262ALBANY,NYALBANYNY 3/22/06 1/0/00
13NORTHERNPLAINS2832ALBERTLEAALBERTLEAMN  1/0/00
14CENTRAL1940ALGONQUINALGONQUINILyes 1/0/00
15NJMETRO4108ALLENTOWNWHITEHALLPA  1/0/00
16NJMETRO4140ALLENTOWNSOUTHALLENTOWNPA  1/0/00
Northern


After the sort.<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=10><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Copy of Master Audit Summary Tracker.xls</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl2002 XP : OS = Windows XP</FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=10><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp (<U>A</U>)bout</TD><TD vAlign=center align=right><FORM name=formCb059465><INPUT onclick='window.clipboardData.setData("Text",document.formFb543420.sltNb142651.value);' type=button value="Copy Formula" name=btCb290334></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=10><TABLE border=0><TBODY><TR><FORM name=formFb543420><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb543420.txbFb965317.value = document.formFb543420.sltNb142651.value" name=sltNb142651><OPTION value="=IF(ISERROR(INDEX('Green Stores'!$A$1:$B$285,MATCH(B1,'Green Stores'!$A$1:$A$285,0),2)),"",INDEX('Green Stores'!$A$1:$B$285,MATCH(B1,'Green Stores'!$A$1:$A$285,0),2))" selected>F1<OPTION value='=IF(I1>0,I1,"")'>H1<OPTION value='=IF(ISERROR(INDEX(Master!$J$2:$J$2048,MATCH(B1,Master!$D$2:$D$2048,0)))," ",INDEX(Master!$J$2:$J$2048,MATCH(B1,Master!$D$2:$D$2048,0)))'>I1<OPTION value="=IF(ISERROR(INDEX('Green Stores'!$A$1:$B$285,MATCH(B2,'Green Stores'!$A$1:$A$285,0),2)),"",INDEX('Green Stores'!$A$1:$B$285,MATCH(B2,'Green Stores'!$A$1:$A$285,0),2))">F2<OPTION value='=IF(I2>0,I2,"")'>H2<OPTION value='=IF(ISERROR(INDEX(Master!$J$2:$J$2048,MATCH(B2,Master!$D$2:$D$2048,0)))," ",INDEX(Master!$J$2:$J$2048,MATCH(B2,Master!$D$2:$D$2048,0)))'>I2<OPTION value="=IF(ISERROR(INDEX('Green Stores'!$A$1:$B$285,MATCH(B3,'Green Stores'!$A$1:$A$285,0),2)),"",INDEX('Green Stores'!$A$1:$B$285,MATCH(B3,'Green Stores'!$A$1:$A$285,0),2))">F3<OPTION value='=IF(I3>0,I3,"")'>H3<OPTION value='=IF(ISERROR(INDEX(Master!$J$2:$J$2048,MATCH(B3,Master!$D$2:$D$2048,0)))," ",INDEX(Master!$J$2:$J$2048,MATCH(B3,Master!$D$2:$D$2048,0)))'>I3<OPTION value="=IF(ISERROR(INDEX('Green Stores'!$A$1:$B$285,MATCH(B4,'Green Stores'!$A$1:$A$285,0),2)),"",INDEX('Green Stores'!$A$1:$B$285,MATCH(B4,'Green Stores'!$A$1:$A$285,0),2))">F4<OPTION value='=IF(I4>0,I4,"")'>H4<OPTION value='=IF(ISERROR(INDEX(Master!$J$2:$J$2048,MATCH(B4,Master!$D$2:$D$2048,0)))," ",INDEX(Master!$J$2:$J$2048,MATCH(B4,Master!$D$2:$D$2048,0)))'>I4<OPTION value="=IF(ISERROR(INDEX('Green Stores'!$A$1:$B$285,MATCH(B5,'Green Stores'!$A$1:$A$285,0),2)),"",INDEX('Green Stores'!$A$1:$B$285,MATCH(B5,'Green Stores'!$A$1:$A$285,0),2))">F5<OPTION value='=IF(I5>0,I5,"")'>H5<OPTION value='=IF(ISERROR(INDEX(Master!$J$2:$J$2048,MATCH(B5,Master!$D$2:$D$2048,0)))," ",INDEX(Master!$J$2:$J$2048,MATCH(B5,Master!$D$2:$D$2048,0)))'>I5<OPTION value="=IF(ISERROR(INDEX('Green Stores'!$A$1:$B$285,MATCH(B6,'Green Stores'!$A$1:$A$285,0),2)),"",INDEX('Green Stores'!$A$1:$B$285,MATCH(B6,'Green Stores'!$A$1:$A$285,0),2))">F6<OPTION value='=IF(I6>0,I6,"")'>H6<OPTION value='=IF(ISERROR(INDEX(Master!$J$2:$J$2048,MATCH
 

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
538
cont.....
Copy of Master Audit Summary Tracker.xls
ABCDEFGH
1CENTRAL2030VALPARAISOVALPARAISOINyes38848
2NJMETRO1286HAMBURGHAMBURGNY 38847
3NJMETRO974MANTUAMANTUANJ 38854
4NJMETRO942WASHINGTONTWSP/MONROESICKLERVILLENJ 38855
5NJMETRO1244GREECEGREECENY 38860
6NJMETRO1268LOCKPORTLOCKPORTNY 38861
7OHIOVALLEY3817STRONGSVILLESTRONGSVILLEOH 38854
8OHIOVALLEY2703CANTONCANTONMI 38860
9CENTRAL198647THANDWESTERNCHICAGOILyes 
10NJMETRO975ABSECONABSECONNJ  
11NJMETRO1241ALBANYALBANYNY  
12NJMETRO1262ALBANY,NYALBANYNY 3/22/06 
13NORTHERNPLAINS2832ALBERTLEAALBERTLEAMN  
14CENTRAL1940ALGONQUINALGONQUINILyes 
15NJMETRO4108ALLENTOWNWHITEHALLPA  
16NJMETRO4140ALLENTOWNSOUTHALLENTOWNPA  
Northern



My question is, how do I forced the range in the second sort (A1:I8) automatically? This range is going to be increasing as more dates are added to column I.

I used this formula to find the last cell with data thinking I could some how reference it in the macro.

=MATCH(9.99999999999999E+307,H:H)-CELL("Row",H1)+1

Code:
 

Forum statistics

Threads
1,141,758
Messages
5,708,358
Members
421,566
Latest member
7Nabisco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top