Dynamic Range for VBA manipulations

I am working on a large macro that is currently working fine with a few spots that have a 'hardwired' range (such as A1:C1000). I originally 'hard-wired' the ranges just to get the macro working for BETA use. I am going back and trying to remove the specific ranges so the macro can find the correct volume of data and the size won't matter. Some of the areas I have changed are working fine, but I am having trouble getting other changes to work.

For example I have changed

Sheets("PLOT DATA").Activate
    Range("A2:O1000") = ""

to

With Sheets("PLOT DATA")
        Set clearstart = .Range(.Range("A2"), .Range("A2").End(xlDown))
        Set clearend = clearstart.Offset(, 14)
        Range(clearstart, clearend) = ""
    End With

, which works fine.

My plot uses

Dim rChartXData As Range
    Dim rChartYData As Range
    Worksheets("PLOT Data").Cells(6, 1).Select                          'select a single cell as the initial plot data

    
    On Error Resume Next                                                ' remove existing chart if exists
    Application.DisplayAlerts = False
    ActiveWorkbook.Charts("GRAPH By System").delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    
    With ActiveSheet                                                    ' determine chart data ranges using only actual data
        Set rChartXData = .Range(.Range("G2"), .Range("G2").End(xlDown))
        Set rChartYData = rChartXData.Offset(, 1)
    End With

    
    With ActiveSheet.Shapes.AddChart.Chart                              'create new chart in PLOT DATA
        .ChartArea.ClearContents                                        'clear any existing chart contents
        
        With .SeriesCollection.NewSeries                                'add the new series of selected data
         m = Sheets("REPORT").Cells(2, "I")
         yr = Sheets("REPORT").Cells(2, "J")
         .Name = m & " " & yr
            .Values = rChartYData
            .XValues = rChartXData
        End With

which also works.
Selecting my print area also works (I found that function on the forum)

With Sheets("TEST1")
    .PageSetup.PrintArea = "$D$1:$M$" & _
        .Range("A:A").Find("*", .Range("A1"), xlValues, xlWhole, xlByRows, xlPrevious).Row
End With
Sheets("TEST1").PrintOut
    
'Print out Unit 2 CR list
With Sheets("TEST2")
    .PageSetup.PrintArea = "$D$1:$M$" & _
        .Range("A:A").Find("*", .Range("A1"), xlValues, xlWhole, xlByRows, xlPrevious).Row
End With
    Sheets("TEST2").PrintOut

also works.
The part that doesn't work: Before the plot section, I have to sort some data. My sort works with the specific range, but I can't get a dynamic range to work. The sort I am trying to use the dynamic range in is:

 Worksheets("SORT DATA").Activate                                'select the SORT DATA information
    Columns("A:G").Select                                           'Remove un-needed columns (leave sys/cat/SL)
    Application.CutCopyMode = False
    Selection.delete Shift:=xlToLeft
    Columns("D:F").Select
    Selection.delete Shift:=xlToLeft
    
    Columns("A:C").Select                                            'sorts data by system, then sub-cat, then SL
 
     ActiveWorkbook.Worksheets("SORT DATA").sort.SortFields.Clear
     ActiveWorkbook.Worksheets("SORT DATA").sort.SortFields.Add Key:=Range( _
         "A2:A1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortNormal
     ActiveWorkbook.Worksheets("SORT DATA").sort.SortFields.Add Key:=Range( _
         "B2:B1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortNormal
     ActiveWorkbook.Worksheets("SORT DATA").sort.SortFields.Add Key:=Range( _
         "C2:C1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortNormal
 
     With ActiveWorkbook.Worksheets("SORT DATA").sort
        .SetRange Range("A1:C1000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

I've tried different versions of the methods used in other sections fo the macro in this last section, but to no avail. The most recent attempt

With Sheets("SORT DATA")
        Set sortstart = .Range(.Range("A2"), .Range("A2").End(xlDown))
        Set sortend = sortstart.Offset(, 2)
'        Range(sortstart, sortend) = ""
    .SetRange Range(sortstart, sortend)
End With
    
Worksheets("SORT DATA").Activate                                'select the SORT DATA information
    
    Columns("A:G").Select                                           'Remove un-needed columns (leave sys/cat/SL)
    Application.CutCopyMode = False
    Selection.delete Shift:=xlToLeft
    Columns("D:F").Select
    Selection.delete Shift:=xlToLeft
    
    Columns("A:C").Select                                            'sorts data by system, then sub-cat, then SL
 
     ActiveWorkbook.Worksheets("SORT DATA").sort.SortFields.Clear
     ActiveWorkbook.Worksheets("SORT DATA").sort.SortFields.Add Key:=Range( _
         "A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortNormal
     ActiveWorkbook.Worksheets("SORT DATA").sort.SortFields.Add Key:=Range( _
         "B:B"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortNormal
     ActiveWorkbook.Worksheets("SORT DATA").sort.SortFields.Add Key:=Range( _
         "C:C"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortNormal
 
     With ActiveWorkbook.Worksheets("SORT DATA").sort
        .SetRange Range("A1:C1000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

(above) fails at

.SetRange Range(sortstart, sortend)

I've been working on this off and on for over two weeks and it is time to ask for help. Please help...

The last time I tried to upload a file, I couldn't, FYI in case you were wondering.

@TM Shucks, thanks! That is shorter.

@nilem, Thank you, that worked in the sopt I had posted. I almost forgot that there is a unique sort that also needs fixed, but I want to see if I can adjust what you've given me on my own (helps me understand better). Still working on it...

Very awesome help!Ya'll rock!

And stuck again. The part that I forgot to include on the first post that needed changed was

 Sheets("SORT DATA").Select
    Columns("A:C").Select
    Range("C1").Activate
    Range("A1:C1000").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Range("A1:C1000").Select
    Selection.Copy
    Sheets("PLOT DATA").Select
    Range("A1").Select
    ActiveSheet.Paste
    [a1] = "SYS"
    [b1] = "Sub-Cat"
    [c1] = "SL"
    [d1] = "SYS-CAT"
    [e1] = "# Graded"
    Sheets("SORT DATA").Select
    Columns("A:C").Select
    Application.CutCopyMode = False
    ActiveSheet.ShowAllData
    [a1] = "SYS"
    [b1] = "Sub-Cat"
    [c1] = "SL"

My attempt that made it the farthest through the code was

With Sheets("SORT DATA")                                        'sorts data by system, then sub-cat, then SL
    With .Range("A2:C" & .Cells(Rows.Count, 1).End(xlUp).Row)
              .AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    End With
End With
    
Sheets("PLOT DATA").Activate
    Range("A1").Select
    ActiveSheet.Paste
    [a1] = "SYS"
    [b1] = "Sub-Cat"
    [c1] = "SL"
    [d1] = "SYS-CAT"
    [e1] = "# Graded"
    Sheets("SORT DATA").Activate
    Columns("A:C").Select
    Application.CutCopyMode = False
    ActiveSheet.AdvancedFilter Action:=xlFilterInPlace, Unique:=False
    [a1] = "SYS"
    [b1] = "Sub-Cat"
    [c1] = "SL"

It hangs up at

    ActiveSheet.AdvancedFilter Action:=xlFilterInPlace, Unique:=False

I was trying to modify the original three level filter to the unique filter. The original script was a recorded macro. When I tried that line with the original

   ActiveSheet.ShowAllData

It didn't work, hence my above attempt.

I really wanted to figure it out, but I think I have tried everything my (very) limited knowledge can come up with.

In the past I haven't been able to upload attachments other than pictures. (And I never got a response from IT help about it). I just tested it again and a small xcel file worked. So I will see if I can make an example and upload it.

Frack...I have a test file and now it won't let me upload a file again. FYI, the message I get is that a security token is missing.

So, unfortunately, no sample file. I really wish I could...

To sum up what is going on, there are three columns of data per row beginning on row 2. Column A is a system label, column B is a sub-category, and column C is a number formatted as text (so that a 4.10 doesn't get confused with a 4.1). Some rows are identical to other rows, and some are a unique set of data. I need to find the unique rows of data (with all three columns staying with that one 'entry' per row). When I find the unique rows, that is copied to another page and then I re-expand all the data. The next piece of code compares the unique data on the plot tab to the data on the sort tab so it can count how many of a unique set were in the original data. The net result of how many unique sets of each data set is what is plotted on a graph.

Again, I wish I could give an actual test example.

So "missing a security token" actually means "file too large" in my case.

I have attached an excerpt of the workbook that includes the data of concern. I have been able to follow the suggested code thus far. What I can't figure out is why the code is now failing on an Activate command. The main sheet (the real one) works up to the filtUNQCOPY routine. With the old code in filtUNQCOPY, the rest of the routine runs fine.

Bump, no response.

@snb, I think I might have figured out what the previous suggest was trying to do. Was your previous suggestion using the split to label the headers and get rid of the Activate that I was using the insert the headers?

I ask because I agree with your signature, I never put code I don't understand onto my computer.

Any thoughts? I can't figure out why a dynamic range selection works for one method and not another. I tried both of the previous suggested methods (which work fine elsewhere in the macro) but could not make either work in this part. Still stumped.

Bump, no response.