Using Named Range instead of Column reference - VBA

[spydey]

There is a bit of code I am using to remove rows where if certain text in the column is not present, the row is deleted, thus leaving only the header and the rows that do contain the required text.

The code is as follows and works quite well:

Sub DeleteRows()Dim r As Long, lr As Long
lr = ActiveWorkbook.Worksheets("Test").Cells(Rows.Count, "[B]A[/B]").End(xlUp).Row
For r = lr To 2 Step -1
  If InStr(Cells(r, "A"), "[B]TEXT[/B]") = 0 Then Rows(r).Delete
Next r
End Sub

I would like to change the reference from, in this example, column "A", to a previously named range. It would simplify things if the columns were to ever be moved around, so that the code would continue to work.

Here is what I was basically aiming for:

Sub DeleteRowsNR()
Dim r As Long, lr As Long
lr = ActiveWorkbook.Worksheets("Test").Cells(Rows.Count, Range("[B]TEXT_RANGE[/B]")).Row
For r = lr To 2 Step -1
  If InStr(Cells(r, "A"), "[B]TEXT[/B]") = 0 Then Rows(r).Delete
Next r
End Sub

I believe that I have my syntax wrong in the following location:

lr = ActiveWorkbook.Worksheets("Trial").Cells(Rows.Count, Range("[B]TEXT_RANGE[/B]")).Row

I just don't see what I have wrong and how to fix it.

Any advice or direction you might provide would be of great help.

-Spydey

 

[RoryA]

What exactly is Range("TEXT_RANGE")? Is it the whole range you want to loop through, or just a header cell to identify the column to process, or something else?

 

[spydey]

What exactly is Range("TEXT_RANGE")? Is it the whole range you want to loop through, or just a header cell to identify the column to process, or something else?

That is the name of the actual range that I have defined. Currently it ranges from A1:A54, but there will be times when it could be several thousand rows long and the column may be moved from column A to a different location on the same sheet.

The header is labeled something different than "TEXT_RANGE".

Sorry about the confusion.

-Spydey

 

[mikerickson]

If you want to look through the first column of Text_Range

With Range("Text_Range").Columns(1)
    LR = .Columns(1).Cells(Rows.Count,1).End(xlUp).Row

    For r = LR To .Row + 1 Step -1
        If InStr(.Cells(r, 1), "TEXT") = 0 Then .Rows(r).EntireRow.Delete
    Next R
End With

 

[spydey]

If you want to look through the first column of Text_Range

With Range("Text_Range").Columns(1)
    LR = .Columns(1).Cells(Rows.Count,1).End(xlUp).Row

    For r = LR To .Row + 1 Step -1
        If InStr(.Cells(r, 1), "TEXT") = 0 Then .Rows(r).EntireRow.Delete
    Next R
End With

Fantastic!! Thank you very much Mike!!!

Out of curiosity, how would I change the "TEXT" to a variable based upon an array I have?

I have an array variable that I want to use in place of the "TEXT". I was thinking of taking the code you adjusted (thank you again for that) and placing it in my other code I already have so that I can use the array variable instead of the "TEXT" constant. Does that make sense?????

I was thinking of something like this:

.........

   Set rng = ThisWorkbook.Worksheets("Summary").Range("A3:B10")
       
    Tracking = rng.Value


    For i = LBound(Tracking) To UBound(Tracking)
    
   ThisWorkbook.Worksheets.Copy


    With Range("Text_Range").Columns(1)
    LR = .Columns(1).Cells(Rows.Count,1).End(xlUp).Row


    For r = LR To .Row + 1 Step -1
        If InStr(.Cells(r, 1), [B]Tracking(i, 2)[/B]) = 0 Then .Rows(r).EntireRow.Delete
    Next R
End With
    
    ActiveWorkbook.Close


    Next i

End Sub

Also, should this be a question for a separate thread????

-Spydey

 

[spydey]

Actually, I just tested it and it seems to have worked for what I needed. Thank you again Mike for your time and assistance!! You gave me a tool and me to refine my process. Thank you.

-Spydey