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]

So what you want is in "Cars" Workbook, "Large" Worksheet...
I have no idea what you want after that with the cell.

 

[VBAProIWish]

Sorry if my post was confusing.

That's where I would like to the sub to get it's value from.

In other words, go to Cars/Large/A2, get that value and use that value instead of "green".

So if the value in Cars/Large/A2 is "purple", the sub would use "purple" as the criteria in which to delete a row.

Hope that helps.

 

[kpark91]

so any rows containing ANY cells with the word from Cars/Large/A2 would need to be deleted? or is there a certain column that has the value "purple"?

 

[HRIS]

How about something along the lines of:

Case "='C:\My Folder\[workbookname.xls]Sheet1'!R2C1"

Obviously you'd need to change the reference in the code to your workbook. I'm new to vba, so not certain if it works in your situation, but thought it might.

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]

Try this, this code requires both workbooks to be open tho and needs "color" column header to be present.
It wasn't tested.

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 Workbooks("Cars").Worksheets("Large").Range("A2').Value                 .Rows(rCount).EntireRow.Delete
        End Select
    Next
End With

Edit1:
Just like the person said before me, if you do not want both workbooks to be open.
You can use open functions in VBA to open up a file and close it everytime the code runs.
But in the code posted, it requires the user to keep the two files opened.

 

[VBAProIWish]

It would be from the column "Color" in the code above as listed here...

    nCol = Application.Match("Color", .Rows(1), 0)

But, if it's easier to refer to the column by position, that's ok as well. It would be column A.

 

[VBAProIWish]

Yes, I will need both workbooks open. This code will be in the middle of HUGE macro with over 30 called modules and it works perfectly so far.

One of the workbooks is always open but hidden, the other is created and opened once a month but its name always changes to accommodate the current year/month.

Thanks

 

[kpark91]

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 Workbooks("Cars").Worksheets("Large").Range("A2').Value
                .Rows(rCount).EntireRow.Delete
        End Select
    Next
End With
 
End Sub

Edit: sorry, didn't know the question was done and I realized the code there was wrong..

 

[VBAProIWish]

Okay Fellas,

Thanks to Both! 80% there!

That's what I wanted, while I have you both thinking along those lines, I would like one slight modification to that.

Instead of the workbook named "Cars", I realized that my "Cars" workbook will have a different name every month. For example, last month it was "2011-04, Cars", this month it will be "2011-05, Cars".

The macro will always be executed from a "2011-xx, Cars" workbook. I have some code that sets the activeworkbook with a name.

Set wb = ActiveWorkbook

So, instead of this line of code here...

Case Workbooks("Cars").Worksheets("Large").Range("A2').Value

If we set WB as the ActiveWorkbook, what would the syntax be in order to say this here?

Case Workbooks("[SIZE=3][COLOR=red][B]WB[/B][/COLOR][/SIZE]").Worksheets("Large").Range("A2').Value

That way the macro will work on whatever workbook names I happen to be using at that time.

I hope that makes sense, I will clarify if you need me to, just ask!

Thanks so much