Delete Rows by Cell Value - Delete sheets w/out prompt

Hi, I have a macro that formats data exported from a database that I can then copy and paste into another spreadsheet which pulls the raw data into a pivot table.

The problem:
I keep having to edit the rows in the Macro to delete as the data is different from week to week. I either need to be able to prompt for the row to edit, or preferrably, have an conditional statement that looks for a text value in column A (like "DUDE") and deletes all rows, incuding that one, and below....THEN same code again but this time looking for a different value in column A (like"SWEET") and deleting all rows, including that one, up through row 11.

So lets say the data was 5000 rows with a header column on row 10. I want to delete all rows DUDE - 5000 and then all rows 11 - SWEET, but I would like the macro to look for those words to base the end/start points of the row deletes.

Here is the code I have so far. There is some other formatting in there as well, like deleting extra sheets. ALSO: is there a way to stop excel from prompting on asking me if I was to delete a sheet when it is in the Macro to do so?

Sub Weekly_Usage()
'
' Weekly_Usage Macro
'
'
    Rows("1:6").Select
    Sheets("KMA Charts").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("Company Summary").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("Dept Level Summary").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("Company Details").Select
    ActiveWindow.SelectedSheets.Delete
    Rows("4997:6000").Select 'Change LOWER number to same row as "DUDE"
    Selection.Delete Shift:=xlUp
    Rows("11:4585").Select 'Change HIGHER number to same row as "SWEET"
    Selection.Delete Shift:=xlUp
    Rows("1:9").Select
    Selection.Delete Shift:=xlUp
    Columns("A:C").Select
    Selection.Delete Shift:=xlToLeft
    Cells.Select
    Selection.RemoveSubtotal
    Columns("A:M").Select
    Selection.UnMerge
    Columns("H").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("A:M").EntireColumn.AutoFit
    Range("F2").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Tech Level Details").AutoFilter.Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Tech Level Details").AutoFilter.Sort.SortFields.Add _
        Key:=Range("F1:F815"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Tech Level Details").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("F:F").Select
    Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Selection.NumberFormat = "0000"
    Range("F2").Select
    Selection.End(xlDown).Select
    
End Sub

Something like this, notice all the "selecting" that's been removed?:

Option Explicit

Sub Weekly_Usage()
Dim RwStart As Long
Dim RwEnd As Long

Application.DisplayAlerts = False

'Delete sheets
    Sheets("KMA Charts").Delete
    Sheets("Company Summary").Delete
    Sheets("Dept Level Summary").Delete
    Sheets("Company Details").Delete
    
'Delete Rows
    RwStart = Cells.Find("Dude", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Row
    RwEnd = Cells.Find("Sweet", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Row

    Range("A1:A9,A11:A" & RwEnd & ",A" & RwStart & ":A5000").EntireRow.Delete Shift:=xlUp
    
'Reformat remainder
    Columns("A:C").Delete Shift:=xlToLeft
    Cells.RemoveSubtotal
    Columns("A:M").UnMerge
    Columns("H").Delete Shift:=xlToLeft
    Columns("F").Delete Shift:=xlToLeft
    Columns("C").Delete Shift:=xlToLeft
    Columns("A:M").EntireColumn.AutoFit
    
    Range("F2").AutoFilter
        ActiveWorkbook.Worksheets("Tech Level Details").AutoFilter.Sort.SortFields. _
            Clear
        ActiveWorkbook.Worksheets("Tech Level Details").AutoFilter.Sort.SortFields.Add _
            Key:=Range("F1:F815"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Tech Level Details").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Columns("F:F").TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 1), TrailingMinusNumbers:=True
        Columns("F:F").NumberFormat = "0000"
    
    Range("F2").Select
    Selection.End(xlDown).Select
End Sub

I am getting an RunTime Error '91' on the RwStart line stating the Object Variable or With block variable not set. I have not changed your code except to update the value of the cell to the actual value I am looking for. I did notice that the value will be in Column D not A, but it looks like your code is watching for the value in xlWhole spreadsheet then grabbing that row number so I don't think it matters where the DUDE or SWEET values are at, correct?

Option Explicit
Sub Weekly_VOD_Usage_MSO()
'
' Weekly_VOD_Usage_MSO Macro
'
' This macro handles the Everstream Welcome Video Usage Weekly File (MSO).
'
'
Dim RwStart As Long
Dim RwEnd As Long

Application.DisplayAlerts = False

'Delete Sheets
    Sheets("KMA Charts").Delete
    Sheets("Company Summary").Delete
    Sheets("Dept Level Summary").Delete
    Sheets("Company Details").Delete

'Delete Rows
    RwStart = Cells.Find("Texas", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Row
    RwEnd = Cells.Find("Northwest", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Row

xlWhole refers to the value in the cell. xlWhole means the value searched has to be found and be equal to the whole cell value. xlPart would be mean it can match to "part" of any string in any cell.

The part that says Cells.Find is the part that makes it search the whole spreadsheet.

You'll need to post a sample workbook so I can compare what the code is designed to do against what your sheet actually contains.