Delete rows with "XYZ" Text in Column

[VBAProIWish]

Hello All,

I have code below that deletes any row that has the text value "green" in the "Color" column and it works perfectly. But now I would like to make one modification to it.

Instead of it already having a pre-populated text value in there (in this case, it is currently "green") I would like the code below to get its text value here...

Workbook Name : Cars
Worksheet Name: Large
Cell Position : A2


So, instead of this....

Sub Delete_rows_with_text_x_in_col_x()
 
Dim cell_to_check As Variant
Dim range_to_check As Range
Dim nCol As Long
Dim rCount As Long
 
With ActiveSheet
    nCol = Application.Match("Color", .Rows(1), 0)
    For rCount = .UsedRange.Rows.Count To 2 Step -1
        Select Case .Cells(rCount, nCol).Value
            Case "[B][SIZE=4][COLOR=red]green[/COLOR][/SIZE][/B]"
                .Rows(rCount).EntireRow.Delete
        End Select
    Next
End With

I would like THIS...

Sub Delete_rows_with_text_x_in_col_x()
 
Dim cell_to_check As Variant
Dim range_to_check As Range
Dim nCol As Long
Dim rCount As Long
 
With ActiveSheet
    nCol = Application.Match("Color", .Rows(1), 0)
    For rCount = .UsedRange.Rows.Count To 2 Step -1
        Select Case .Cells(rCount, nCol).Value
            Case "[B][SIZE=4][COLOR=#ff0000]WB=Cars, WS=Large, Cell=A2[/COLOR][/SIZE][/B]"
                .Rows(rCount).EntireRow.Delete
        End Select
    Next
End With
 
End Sub

Can this be done?


Thanks

 

[kpark91]

I believe the syntax would be

Set wb = Workbooks("2011-xx, Cars")
Case Workbooks(wb).Worksheets("Large").Range("A2').Value

I don't think using ActiveWorkbook would be explicit enough so it may cause unwanted modifications in the wrong workbook.
So, I think the above code would be more stable.

 

[VBAProIWish]

kpark,

Thanks for the reply. Won't that search ONLY for "2011-xx, Cars" though?

Correct me if I'm wrong but my thought is that as long as I put this line RIGHT before all this code, I should be ok?

Sheets("2011-whatevermonth, CAR").Select
 
rest of code

That way, I'd be gauranteed to be setting the correct workbook as "WB"

If that seems ok, would the syntax then be like this here?...

Set wb = ActiveWorkbook
Case Workbooks(wb).Worksheets("Large").Range("A2').Value

Your thoughts?

 

[kpark91]

1)Yes that would only search for "2011-XX, CAR"

If you want it fully automatic, you will need additional functions which may be a hassle.

2) If your 'rest of code' is trying to do something on your workbook that is not "2011-XX, CAR", having Sheets("2011-whatevermonth, CAR").Select before 'rest of code' would be wrong. and it will especially be wrong if the worksheet, named "2011-whatevermonth, CAR", is not in the workbook you're working on.

But yes, syntax would be ok and your code will work fine
Except for .Range("A2') part..

Set wb = ActiveWorkbook
Case Workbooks(wb).Worksheets("Large").Range("A2").Value

 

[VBAProIWish]

Hmm,

I tried this code right here and I got

"Runtime error 9, subscript out of range"

Sub aaa_test_macro_02()
 
Sheets("2011-05, Cars").Select
 
 Set wb = ActiveWorkbook
 
 
    Windows("History").Activate
 
 
Dim cell_to_check As Variant
Dim range_to_check As Range
Dim nCol As Long
Dim rCount As Long
 
With ActiveSheet
    nCol = Application.Match("Color", .Rows(1), 0)
    For rCount = .UsedRange.Rows.Count To 2 Step -1
        Select Case .Cells(rCount, nCol).Value
  Case Workbooks(wb).Worksheets("Large").Range("A2").Value 
 .Rows(rCount).EntireRow.Delete
        End Select
    Next
End With
 
 
End Sub

 

[kpark91]

Sub aaa_test_macro_02()
 
    'Which workbook are you trying to delete rows in? and which workbooks are you using?
 
    Sheets("2011-05, Cars").Activate
    Set wb = Workbooks("Car").Sheets("2011-05, Cars") 'Just to simplify
 
    Windows("History").Activate 'What is this?
 
 
    Dim cell_to_check As Variant
    Dim range_to_check As Range
    Dim nCol As Long
    Dim rCount As Long
 
    With ActiveSheet
        nCol = Application.Match("Color", .Rows(1), 0) 'capitals matter
        For rCount = .UsedRange.Rows.Count To 2 Step -1
            Select Case .Cells(rCount, nCol).Value
                Case wb.Range("A2").Value 
                  .Rows(rCount).EntireRow.Delete
            End Select
        Next
    End With
 
End Sub

 

[VBAProIWish]

kpark,

Yeah I understand that might've been confusing now. Okay, here's the code that works perfectly when both workbooks have a static name...

Sub aaa_test_macro_02()
 
 
'I already have the dynamically named workbook open at this point
'and am currently within that workbook named "2011-05, Cars"
 
 
Sheets("Car Color").Select 'This goes to the WS that has the value
 
 Set wb = ActiveWorkbook 'This references the "2011-05, Cars" WB as "wb"
 
 
    Windows("Car History.xlsm").Activate 'This brings me to the "Car History" WB
 
 
'  Sub Delete_rows_with_text_x_in_col_x()
 
 
Dim cell_to_check As Variant
Dim range_to_check As Range
Dim nCol As Long
Dim rCount As Long
 
With ActiveSheet
 
'Note that there are actually 2 "Car Color" references here.
'One is a column name in the "Car History" workbook, the other is a WS in the "2011-05, Cars" WB.
 
 
    nCol = Application.Match("Car Color", .Rows(1), 0)
    For rCount = .UsedRange.Rows.Count To 2 Step -1
        Select Case .Cells(rCount, nCol).Value
Case Workbooks("[SIZE=3][COLOR=red][B]2011-05, Cars[/B][/COLOR][/SIZE]").Worksheets("Car Color").Range("A2").Value
 .Rows(rCount).EntireRow.Delete
        End Select
    Next
End With  
 
End Sub

So, that works, I just need to modify the part in bold in red to point to the workbook set as "wb" instead of "2011-05, Cars"


Hope that makes sense.

Thanks again for your help on this.

 

[VBAProIWish]

Hard to believe, but I figured it out myself!


It was this line here that needed to be corrected...

Case Workbooks("[COLOR=red][B]2011-05, Cars[/B][/COLOR]").Worksheets("Car Color").Range("A2").Value

Here is the correct syntax...

Case [COLOR=red][B]wb[/B][/COLOR].Worksheets("Car Color").Range("A2").Value

Thanks guys for all your help, I couldn't have done it without you!