• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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