Error in Macro request to delete all rows between two specific text values whose rows are dynamic

[OpsDirJD]

My goal is to delete all rows between text QA and text QS, including row with text QS. Text is in column B and the number of rows between them is dynamic. All rows without either text should not be deleted.

This is the code I am using (from MrExcel), but I am getting the following error: "Compile error: Variable not defined" and the r in the r=1 code is highlighted.

'Delete rows below QA until you get to QS

Sub DeletebyBookends_KEEP_LAST()
Dim strStart As String, strEnd As String
Dim DELETEMODE As Boolean
Dim DelRng As Range
    strStart = "QA"
    strEnd = "QS"
    
    DELETEMODE = False
    For r = 1 To Range("B" & Rows.Count).End(xlUp).Row  'first to last used row
        
        If Range("B" & r).Value = strStart Then DELETEMODE = True
        If Range("B" & r).Value = strEnd Then DELETEMODE = False
        
        If DELETEMODE Then
            'Create a Delete Range that will be used at the end
            If DelRng Is Nothing Then
                Set DelRng = Range("A" & r)
            Else
                Set DelRng = Application.Union(DelRng, Range("A" & r))
            End If
        End If
        
    Next r
    
    'Delete the Range compiled from above
    If Not DelRng Is Nothing Then DelRng.EntireRow.Delete xlShiftUp
    
End Sub

 

[Joe4]

Welcome to the Board!

Do you have Option Explicit turned on?
If so, it is a good thing, but it means that you must declare ALL variables before using them in your code.
So add this line to the DIM section at the top, and try again:

Dim r as Long

 

[OpsDirJD]

My goal is to delete all rows between text QA and text QS, including row with text QS. Text is in column B and the number of rows between them is dynamic. All rows without either text should not be deleted.

This is the code I am using (from MrExcel), but I am getting the following error: "Compile error: Variable not defined" and the r in the r=1 code is highlighted.

'Delete rows below QA until you get to QS

Sub DeletebyBookends_KEEP_LAST()
Dim strStart As String, strEnd As String
Dim DELETEMODE As Boolean
Dim DelRng As Range
    strStart = "QA"
    strEnd = "QS"
   
    DELETEMODE = False
    For r = 1 To Range("B" & Rows.Count).End(xlUp).Row  'first to last used row
       
        If Range("B" & r).Value = strStart Then DELETEMODE = True
        If Range("B" & r).Value = strEnd Then DELETEMODE = False
       
        If DELETEMODE Then
            'Create a Delete Range that will be used at the end
            If DelRng Is Nothing Then
                Set DelRng = Range("A" & r)
            Else
                Set DelRng = Application.Union(DelRng, Range("A" & r))
            End If
        End If
       
    Next r
   
    'Delete the Range compiled from above
    If Not DelRng Is Nothing Then DelRng.EntireRow.Delete xlShiftUp
   
End Sub

I'm not sure how to edit my post, but I meant to say My goal is to delete all rows between text QA and text QS, including row with text QA

 

[Joe4]

See my comments above.
This is one of the times in which the error message is actually helpful and is telling you exactly what the issue is - you are trying to use a variable that you did not declare first (in a DIM statement). That simple fix I gave you should get rid of that error.

 

[OpsDirJD]

See my comments above.
This is one of the times in which the error message is actually helpful and is telling you exactly what the issue is - you are trying to use a variable that you did not declare first (in a DIM statement). That simple fix I gave you should get rid of that error.

Joe4 Thank you so much!!! That worked perfectly. I knew it would be a simple fix, but I'm still a VBA newbie.

 

[Joe4]

You are welcome. Glad I was able to help!

Option Explicit is a great tool to help you catch typos in your code, but it also means that you must declare all variables before using them.
Here is more information on that: Option Explicit in Excel VBA