VBA find column by name and sort [Solved/Closed]

Posts
6
Registration date
Wednesday April 29, 2015
Status
Member
Last seen
October 13, 2015
-
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 

2 replies

Best answer
approved by Ratnendra Ashok on Aug 22, 2019
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
787
4
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

Say "Thank you" 4

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 5698 users have said thank you to us this month

SidK
Posts
6
Registration date
Wednesday April 29, 2015
Status
Member
Last seen
October 13, 2015
-
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!
Great macro, just what i was looking for! Thanks!
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
787
1
Thank you
your are welcome