Previous Topic Next Topic

VBA find column by name and sort

Solved/Closed
SidK Posts 6 Registration date Wednesday 29 April 2015 Status Member Last seen 13 October 2015 - 3 Jul 2015 à 10:34
 bOFILL - 30 Apr 2019 à 08:16
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!
Related:

2 responses

Réponse 1 / 2
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
6 Jul 2015 à 02:46
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
SidK Posts 6 Registration date Wednesday 29 April 2015 Status Member Last seen 13 October 2015
6 Jul 2015 à 06:14
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!
bOFILL
30 Apr 2019 à 08:16
Great macro, just what i was looking for! Thanks!

Similar discussions

Need to delete columns based on Column header
yesare -
topazfae -

7 responses
Auto generate serial number in excel based on Column
Raj_1562 -
TrowaD -

4 responses
Macro to Select Multiple Columns by Columns Specific Headings to Delete Dupes
jackied25 -
TrowaD -

2 responses
delete all rows except first and last row
urgentMe -
fairbud -

2 responses
Execute Macro On Calculated Cell change
Notar -
rizvisa1 -

5 responses
Excel make cell flash red
hugrl -
Rjc -

13 responses