Solved

Sorting a sheet

Posted on 2011-03-24
15
253 Views
Last Modified: 2012-05-11
Further to the related question,  i have a slight problem with the code that i am using that i would like to change.

The code below uses h3 which is the top cell of the data i want to sort by but if for any reason further columns are inserted then this code isnt going to work.

Can you tell me how to update the code so that it uses the range name for h2 which is LastChange rather than using h3.


Sub y()

Application.ScreenUpdating = False

With Range("h3", Range("h3").End(xlDown))
    .Offset(, 1).EntireColumn.Insert
    .Offset(, 1).Formula = "=IF(ISNUMBER(h3),VALUE(h3),LOOKUP(h3,{""CLOSED"",""n/a""},{1,2}))"
    .Resize(, 2).sort key1:=Range("i1"), order1:=xlDescending, Header:=xlNo
    .Offset(, 1).EntireColumn.Delete
End With

Application.ScreenUpdating = True

End Sub


0
Comment
Question by:GordonMasson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
15 Comments
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35206821
Change Range("h3") to the Range("LastChange") and it should do the trick....
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35206827

Probably like this actually, if you could post a workbook i could validate

With Range(Range("LastRange"), Range("LastRange").End(xlDown))

-brandon
0
 
LVL 9

Expert Comment

by:McOz
ID: 35206833
Use this:
Sub y()

Application.ScreenUpdating = False

With Range("LastChange", Range("LastChange").End(xlDown))
    .Offset(, 1).EntireColumn.Insert
    .Offset(, 1).Formula = "=IF(ISNUMBER(LastChange),VALUE(LastChange),LOOKUP(LastChange,{""CLOSED"",""n/a""},{1,2}))"
    .Resize(, 2).sort key1:=Range("i1"), order1:=xlDescending, Header:=xlNo
    .Offset(, 1).EntireColumn.Delete
End With

Application.ScreenUpdating = True

End Sub

Open in new window

Cheers
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35206892
yea i messed up on mine a lil bit, i put an extra Range() in there

should look as such:

With Range("LastChange", Range("LastChange").End(xlDown))
0
 

Author Comment

by:GordonMasson
ID: 35207164
Thanks...
Something not quire right here tho....
this line:
    .Offset(, 1).Formula = "=IF(ISNUMBER(LastChange),VALUE(LastChange),LOOKUP(LastChange,{""CLOSED"",""n/a""},{1,2}))"
 doesnt seem to do the same as
.Offset(, 1).Formula = "=IF(ISNUMBER(h3),VALUE(h3),LOOKUP(h3,{""CLOSED"",""n/a""},{1,2}))"

does it have to be set up as Range("LastChange") or anything like that?
0
 
LVL 9

Expert Comment

by:McOz
ID: 35207257
Assuming "LastChange" resolves to H3, it should do the same thing. The difference is that "Range("LastChange")" is needed in VBA to refer to the range object, but within the Formula you do not need to use "Range". You just use "LastChange" as you would if you were typing the function in the cell.

Hope this helps
0
 
LVL 9

Expert Comment

by:McOz
ID: 35207303
Gordon, can you tell me how the results from the version using the named range are different?

possibly LastChange no longer refers to H3? Since I don't know how you have set up the named range its hard to tell...
0
 

Author Comment

by:GordonMasson
ID: 35207447
Hi McOz

LastChange is actualy the header for the column so its H2
When i use the updated code all the dates in the inserted column change to 01 January 1900
When i use the original code the dates in the inserted column are kept as the original dates and the other values change to 01 January 1900

If you look at the sample worksheet here http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26893797.html
you will see how its meant to work
0
 
LVL 9

Expert Comment

by:McOz
ID: 35207640
OK, sorry I did not take the time to see what the code was really doing before!
Try this, using formulaR1C1 to use relative references:
Sub y() 
 
Application.ScreenUpdating = False 
 
With Range("LastChange", Range("LastChange").End(xlDown)) 
    .Offset(, 1).EntireColumn.Insert 
    .Offset(, 1).FormulaR1C1 = "=IF(ISNUMBER(RC[-1]),VALUE(RC[-1]),LOOKUP(RC[-1],{""CLOSED"",""n/a""},{1,2}))" 
    .Resize(, 2).sort key1:=Range("i1"), order1:=xlDescending, Header:=xlNo 
    .Offset(, 1).EntireColumn.Delete 
End With 
 
Application.ScreenUpdating = True 
 
End Sub

Open in new window


So the formulas in the new column refer to the corresponding cells in the previous column.
0
 

Author Comment

by:GordonMasson
ID: 35207737
Ah thats it McOz

That works now thanks.
Just one thing i have realised that i should have included in the earlier question.......
This is sorting column H but it should really sort all the data from cell A3...(Cell A2 is caled UnitNumber) for all rows/columns in use.
can i add a simple change to this code to sort all the active cells or should i ask a new question?

Thanks
0
 
LVL 9

Expert Comment

by:McOz
ID: 35207864
Without testing, I think you could just change it to the following to sort all columns:
Sub y()  
  
Application.ScreenUpdating = False  
  
With Range("LastChange", Range("LastChange").End(xlDown))  
    .Offset(, 1).EntireColumn.Insert  
    .Offset(, 1).FormulaR1C1 = "=IF(ISNUMBER(RC[-1]),VALUE(RC[-1]),LOOKUP(RC[-1],{""CLOSED"",""n/a""},{1,2}))"  
    .Offset(,-(.Column - 1)).Resize(, (.Column + 1)).sort key1:=Range("i1"), order1:=xlDescending, Header:=xlNo  
    .Offset(, 1).EntireColumn.Delete  
End With  
  
Application.ScreenUpdating = True  
  
End Sub

Open in new window

Give it a shot!
0
 
LVL 9

Expert Comment

by:McOz
ID: 35207915
Oh, and to make the sort key relative as well, change this:
key1:=Range("i1"),

Open in new window

to this:
key1:=Range("LastChange").Offset(,1),

Open in new window

Cheers
0
 

Author Comment

by:GordonMasson
ID: 35208253
Very close McOz but its sorting the header row...row 2 as well so i am loosing my headers

Any thoughts?

Thanks again for your help
0
 
LVL 9

Accepted Solution

by:
McOz earned 500 total points
ID: 35208333
Yes! change Header:=xlNo to Header:=xlYes in the sort line

Good luck!
0
 

Author Closing Comment

by:GordonMasson
ID: 35431369
Thanks for your help and sorry for the delay in closing this out
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question