Can anyone help me with this?
Can anyone help me with an alphanumeric sorting code for multiple columns sorting (column by column sorting) advance.
Also let me know If the following code can be used for multiple columns:
VB:Sub sortAlphaNumericMulti() Dim i As Long 'Loop counter Dim lrow As Long 'Last row of data Dim nrow As Long 'New lastrow after copy Dim lcol As Long 'Last col of data, this row 'Presumes data in Col A and Row 1 has maximum extents 'Get last row of data Col A (+1 for first blank row) lrow = Range("A65536").End(xlUp).Row + 1 'Variable for compare nrow = lrow 'Get last column of data (row 1) lcol = Range("IV1").End(xlToLeft).Column 'Use empty space as storage, splitting cell values 'Loop on all rows For i = 1 To lrow - 1 'Get alpha Cells(nrow, 1) = Left(Cells(i, 1), 3) 'Get numeric Cells(nrow, 2) = Left(Cells(i, 1), Len(Cells(i, 1)) - 3) 'Copy rest of data Range(Cells(i, 2), Cells(i, lcol)).Copy Cells(nrow, 3) 'Increment row nrow = nrow + 1 Next i 'Sort by numeric then alpha Range(Cells(lrow, 1), Cells(nrow - 1, lcol + 1)).Select Selection.Sort _ Key1:=Range("B" & lrow), Order1:=xlAscending, _ Key2:=Range("A" & lrow), Order2:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom 'Copy/Paste back 'where to work nrow = lrow 'Loop on all cells For i = 1 To lrow - 1 'Rebuild original values Cells(i, 1) = Cells(nrow, 1) & Cells(nrow, 2) 'Copy sorted Range(Cells(nrow, 3), Cells(nrow, lcol + 1)).Copy Cells(i, 2) 'Increment row nrow = nrow + 1 Next i 'Delete temp(working)range Range(Cells(lrow, 1), Cells(nrow - 1, lcol + 1)).ClearContents Application.ScreenUpdating = True End Sub
Last edited by Smallman; June 6th, 2013 at 16:21. Reason: Add code Tags to post
Same rules here as on Excelforum - Please wait a sensible period before bumping a thread.
But a rule more rigorously enforced is:
Rule #4 - Do not cross-post without supplying a link to the duplicate question on the other forum.
You also posted this topic on ExcelForum. While you are free to post where ever, every board like this has a similar rule - you must include links to all you posts on other boards.
Have a read of this to understand the reasons for this rule.
You should update this thread with the URL of any other threads dealing with this particular issue, and ensure future posts by you comply fully with the rules.
Last edited by cytop; June 8th, 2013 at 00:48.
I have attached a sample of requirements.
My data is in the format "# = XXXX" where # can be any number between 1 to 500 and XXXX can any number alphabets. I have to sort the data in descending order based on the digits. I am not concerned about the order of the alphabets. Also, the number of columns in the worksheet is very large (more than 1000). So I cant not work on individual columns. I think only a macro can help me here. I hardly have any experience in coding. The code I shared in my earlier email extract the numbers from a cell and sorts the first column based on the numbers. But it works for a single column only. I am not sure how to modify the code for it to work for 1000 of columns at a go. Thanks in advance.
There are currently 1 users browsing this thread. (0 members and 1 guests)