So..

I would like to make a macro that will do a number of things with
one-click:

1. Automatically fill down a range of rows with a formula; the number
of rows to fill down are the number of rows there are in the A column.

2. Sort the newly created column/range with expanded selection for all
columns.

3. Either clear the row or these calculations could take place in a
hidden column.

I received some assistance over at VBAX.. the code for the formula is
as follows :


Code:
--------------------
Option Explicit

Public Function RetrieveSplitItem(Text As String, Separator As String, Item As Variant, _
Optional CaseSen As Boolean = False)

' Function based on post by Brad Yundt
' http://www.experts-exchange.com/Appl..._21221177.html

' Returns a specified substring from a larger string (Text) separated by a specified
' character sequence (Separator)
Dim X As Variant
If CaseSen Then
X = Split(Text, Separator, -1, vbBinaryCompare)
Else
X = Split(Text, Separator, -1, vbTextCompare)
End If

If IsNumeric(Item) And (Item < 1 Or Item (UBound(X) + 1)) Then
RetrieveSplitItem = CVErr(xlErrNA)
ElseIf Not IsNumeric(Item) And Item < "L" And Item < "l" Then
RetrieveSplitItem = CVErr(xlErrNA)
Else
If Item = "L" Or Item = "l" Then Item = UBound(X) + 1
RetrieveSplitItem = X(Item - 1)
End If

End Function

Public Function StripOutCharType(CheckStr As String, Optional KillNumbers As Boolean = True, _
Optional AllowedChar As String, Optional NeverAllow As String) As String

' Function by Patrick Matthews
' For the given string, the function removes all numeric characters (KillNumbers=True) or
' all non-numeric characters (KillNumbers=False). Use AllowedChar to build a string of override
' characters that are always allowed. For example, "$,." would indicate that the dollar sign,
' comma, and period should all be allowed, even if KillNumbers=False; likewise, "9" would indicate
' that nines should be kept even if KillNumbers=True. NeverAllow is a string of override
' characters that are never allowed. The "never allowed" characters are processed before the
' "always allowed" characters, and so if any characters are in both strings Never allow takes
' precedence

Dim Counter As Long
Dim TestChar As String
Dim TestAsc As Long

' Loop through characters
For Counter = 1 To Len(CheckStr)

' Get current character and its ANSI number
TestChar = Mid(CheckStr, Counter, 1)
TestAsc = Asc(TestChar)

' Test first to see if current character is never allowed
If InStr(1, NeverAllow, TestChar, vbTextCompare) 0 Then
' do nothing

' If current character is in AllowedChar, keep it
ElseIf InStr(1, AllowedChar, TestChar, vbTextCompare) 0 Then
StripOutCharType = StripOutCharType & TestChar

' If KillNumbers=True, test for not being in numeric range for ANSI
ElseIf KillNumbers Then 'only allow non-numbers
If TestAsc < 48 Or TestAsc 57 Then
StripOutCharType = StripOutCharType & TestChar
End If

' If KillNumbers=False, test for being in numeric ANSI range
Else 'only allow numbers
If TestAsc = 48 And TestAsc <= 57 Then
StripOutCharType = StripOutCharType & TestChar
End If
End If
Next

End Function

--------------------


Then the formula using this function is
Code:
--------------------
=RetrieveSplitItem(A1,"-",1)&"-"&StripOutCharType(RetrieveSplitItem(A1,"-",2))&TEXT(StripOutCharType(RetrieveSplitItem( A1,"-",2),FALSE),"00000")

--------------------


The sample data for this is found attached. Column E is the desired
result from the sort.


+-------------------------------------------------------------------+
|Filename: test.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4448 |
+-------------------------------------------------------------------+

--
kronik
------------------------------------------------------------------------
kronik's Profile: http://www.excelforum.com/member.php...o&userid=31541
View this thread: http://www.excelforum.com/showthread...hreadid=521620