VBA Sort Column C A-Z error 438

[Tardisgx]

https://imgur.com/a/tO6cmWG

The issue is the range i'm filtering will vary so the established range C1:C690963 and lower in the macro A1:A690963 need to be undefined. Does that make sense. Besides i'm using the same data set input as yesterday and yesterday I was not receiving an error so maybe there's something else going on as well.

Columns A-C are occupied with headers.

Sub Part13()
' Pull Down List
    Sheets("Sheet2").Select
    Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row).FillDown
    Sheets("Sheet2").Select
        Application.CutCopyMode = False
    Sheets("Sheet2").Select
    Columns("C:C").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 'Pulled down formula list turned into values
        :=False, Transpose:=False
 'below is remove null from desc and images
    Columns("A:C").Select
    Selection.AutoFilter
    Range("D1").Select
    ActiveWorkbook.Worksheets("Sheet2").AutoFilter.Sort.SortFields.Clear
[B]    ActiveWorkbook.Worksheets("Sheet2").AutoFilter.Sort.SortFields.Add2 Key:= _
        Range("C1:C690963"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal[/B]
    With ActiveWorkbook.Worksheets("Sheet2").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("C:C").Select
    Selection.SpecialCells(xlCellTypeConstants, 16).Select
    Range(Selection, Selection.End(xlToLeft).End(xlToLeft)).Select
    Selection.ClearContents

    ActiveWorkbook.Worksheets("Sheet2").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").AutoFilter.Sort.SortFields.Add2 Key:= _
        Range("A1:A690963"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet2").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.AutoFilter
'Endof PART13
End Sub

 

[RoryA]

Are you using a different version of Excel to the one the code was written on? I suspect so, in which case I suggest you change Sortfields.Add2 to Sortfields.Add

 

[Tardisgx]

I didn't think it was a different version but yeah that edit didn't bring up an error and the code ran successfully. Thanks.

Can you help with the other part of my question; how to make the established ranges- dynamic code so it'll just go down to the end of the column?

 

[RoryA]

Try just using Range("C1") and Range("A1") as the key.

 

[Tardisgx]

https://imgur.com/a/xwsxZch Had to add with block also; thanks for your help

 

[RoryA]

That first With block isn't doing anything.

 

[Tardisgx]

I don't know what to say; was still reviving error; this time "Missing with block variable" so I was like ok ok added it and it works. ʅ_(ツ)_ʃ
Reminds me of trying to get a printer to work
"I'm out of ink"
"But I just ga.."
"I'M OUT OF INK!"

 

[RoryA]

You probably had a superfluous End With line - should have removed that rather than adding a new With block.

 

[Tardisgx]

You can see the original code in my first post which had the add2 error and was range specific.

I removed add2 with add which let the code run

Using A1 & C1 as the key instead of specified ranges did not work until i added the With block.

Good idea but I can't see a superfluous end with

 

[RoryA]

If the first code worked and all you did was remove the 2 from the end of Add2, the code would have worked as was, without an additional With block.