Dynamic Range in VBA and Auto Deleting Unwanted Imported Data By Name

[oldeirish]

Hi,

I have created a Macros to input data (from external source) through recording a macro.

Since it is a weekly update, the data varies in size.

How do I change the area in which the data can still be totalled and also delete lines that begin with "Total" instead of "Company" name?

Sub CDS()
'
' CDS Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\xxxxx\Documents\XXX XXX XXX\Imported Data\RevenueReport_.csv" _
        , Destination:=Range("$A$9"))
        .Name = "RevenueReport_"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 10
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 2, 1, 3, 3, 2, 1, 1, 3, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Columns("H:H").ColumnWidth = 9.86
    ActiveWindow.SmallScroll Down:=-9
    Columns("A:A").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A8").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveWindow.SmallScroll Down:=48
    ActiveCell.Offset(53, 0).Range("A1:J1").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=30
    ActiveCell.Offset(28, 0).Range("A1:J1").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=27
    ActiveCell.Offset(30, 0).Range("A1:J1").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=12
    ActiveCell.Offset(18, 0).Range("A1:J1").Select
    Selection.ClearContents
    ActiveCell.Offset(2, 7).Range("A1").Select
    Selection.ClearContents
    ActiveCell.Offset(0, 2).Range("A1").Select
    Selection.ClearContents
    ActiveCell.Offset(0, -3).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-130]C:R[-3]C)"
    ActiveCell.Offset(-130, -6).Range("A1:K132").Select
    ActiveCell.Offset(1, -6).Range("A1").Activate
    Selection.Style = "Comma"
    ActiveCell.Offset(0, 6).Columns("A:A").EntireColumn.ColumnWidth = 14
    ActiveCell.Offset(0, 9).Columns("A:A").EntireColumn.ColumnWidth = 11.43
    ActiveWindow.SmallScroll Down:=-132
    ActiveCell.Offset(0, 6).Columns("A:A").EntireColumn.Select
    Selection.NumberFormat = "$#,##0.00"
    ActiveCell.Offset(0, -3).Columns("A:B").EntireColumn.Select
    Selection.NumberFormat = "m/d/yyyy"
    Selection.ColumnWidth = 12
    Selection.ColumnWidth = 13.86
End Sub

Additionally, I'm receiving this error - ActiveCell.Offset(1, -6).Range("A1").Activate

Thank you kindly for ANY help .... not new to Excel, just new to many many functions!

Thanks