VBA find Column by Name and Sort [Solved/Closed]

SidK 6 Posts Wednesday April 29, 2015Registration date October 13, 2015 Last seen - Jul 3, 2015 at 10:34 AM - Latest reply: venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen
- Jul 7, 2015 at 12:54 AM
Hello,

I am trying to make a code that should:

- Go to the first worksheet
- Find the first column with header "sku"
- Sort this column ascending


However, I just don't seem to get it right.
Error message that I get now = Run-time error "1004".
The sort reference is not valid.


Any advice is appreciated.
Underneath the code I have created so far

(I am not an expert, which may show :))



 
Sub FindColumnSortAscending()


'Activate worksheet 1
ActiveWorkbook.Worksheets(1).Activate


'Search the column with the name "attribute_set"
searchCol = "sku"


'Set a Range called "rng1" to
Dim rng1 As Range
Set rng1 = ActiveSheet.UsedRange.Find(searchCol, , xlValues, xlWhole)


'select range equal to column with header "attribute set"

'rng1.Select

'Apply sorting

ActiveWorkbook.Worksheets(1).AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets(1).AutoFilter.Sort.SortFields.Add Key:=Range("rng1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets(1).AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


End Sub


Thanks very much in advance!
See more 

3 replies

Best answer
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Jul 6, 2015 at 02:46 AM
1
Thank you
you can make it simpler. many of the arguments are default arguments for e.g. ascending order etc.

try this simpler macro

Sub sorting()
Dim col As String, cfind As Range
col = "sku"
Worksheets(1).Activate
col = "sku"
Set cfind = Cells.Find(what:=col, lookat:=xlWhole)
'Set rng1 = Range(cfind, cfind.xlDown)
ActiveSheet.Cells.Sort key1:=cfind, Header:=xlYes
End Sub

Thank you, venkat1926 1

Something to say? Add comment

CCM has helped 1801 users this month

SidK 6 Posts Wednesday April 29, 2015Registration date October 13, 2015 Last seen - Jul 6, 2015 at 06:14 AM
Hi Venkat1926

Thanks so much for your help.
The code you provided works.

I have cleaned the code up slightly:
Col = "sku" was added twice
And I removed 'Set rng1 = Range(cfind, cfind.xlDown)

Sub sorting()
Dim col As String, cfind As Range
Worksheets(1).Activate
col = "sku"
Set cfind = Cells.Find(what:=col, lookat:=xlWhole)
ActiveSheet.Cells.Sort key1:=cfind, Header:=xlYes
End Sub


Thanks again!
Best answer
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Jul 7, 2015 at 12:54 AM
1
Thank you
your are welcome

Thank you, venkat1926 1

Something to say? Add comment

CCM has helped 1801 users this month