• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Sorting a sheet

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
GordonMasson
Asked:
GordonMasson
  • 7
  • 5
  • 3
1 Solution
 
KnutsonBMCommented:
Change Range("h3") to the Range("LastChange") and it should do the trick....
0
 
KnutsonBMCommented:

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

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

-brandon
0
 
McOzCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
KnutsonBMCommented:
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
 
GordonMassonAuthor Commented:
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
 
McOzCommented:
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
 
McOzCommented:
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
 
GordonMassonAuthor Commented:
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
 
McOzCommented:
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
 
GordonMassonAuthor Commented:
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
 
McOzCommented:
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
 
McOzCommented:
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
 
GordonMassonAuthor Commented:
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
 
McOzCommented:
Yes! change Header:=xlNo to Header:=xlYes in the sort line

Good luck!
0
 
GordonMassonAuthor Commented:
Thanks for your help and sorry for the delay in closing this out
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now