Previous Topic Next Topic

VBA find column by name and sort

Solved/Closed
SidK Posts 6 Registration date Wednesday April 29, 2015 Status Member Last seen October 13, 2015 - Updated on Jan 30, 2019 at 06:27 AM
 bOFILL - Apr 30, 2019 at 08:16 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!

2 responses

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

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