VBA - Delete Entire Row if Cell contains Text variable

[1580067]

Hi everybody,

First of all I would like to say hi to the community. As you can see this is my first post... I am new to VBA (and any other programming language quite frankly). I look forward to being able to help fellow coders in their quest to their perfect Excel program once I develop a deeper knowledge and understanding of VBA.

But enough of chit chat

I searched through this website and others for a solution to my problem but all the VBA codes I found do not seem to solve my problem and I can't find out why...

Here is what I ought to do:
I have an excel file in which the cells in column A can take up to 10 Text variables (eg France, Italie, UK...). All the other columns contain data that I need but that are irrelevant to solving the problem I think.
I am trying to delete the entire row if the cell in A contains 7 out of the 10 variables (delete the row if A contains "France" or "Italie" or "UK").
Once the rows deleted I would like to delete 2 columns.

I have tried do some loop that I found online modifying where necessary but I obviously got it wrong.

I would be very grateful if someone could help me out


Have a nice afternoon.

[p45cal]

Lots of questions - do these have to be an exact match of France or Italie for the whole cell, or part of the cell; can the cell contain more than one of these? which columns do you want to delete? etc. etc.
To prevent us guessing wrongly, perhaps upload a small workbook to the cloud and link to it or, if you can, attach it (you may not have enough posts to attach, I've forgotten).

[mokhtar]

try

Sub sbDelete_Rows_IF_Cell_Cntains_String_Text_Value()

Dim LRow As Long
Dim X As Long

LRow = 10

For X = LRow To 1 Step -1
   If Cells(X, "A") = "France" Or Cells(X, "A") = "Italie" Or Cells(X, "A") = "UK" Then    ' You can change this text
      Cells(X, "A").Resize(, 2).ClearContents
   End If
Next

End Sub

[SamT]

Sub SamT_SeleteColumns_byValue()
Dim DelValues As Variant
Dim Del As Boolean
Dim LastRow As Long
Dim Rw As Long
Dim i As Long

DelValues = Array("France", "Italy", "USA", "Etc., etc., etc.")

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

'Delete From bottom up
For Rw = LastRow To 1 Step -1
  For i = LBound(DelValues) To UBound(DelValues)

'Select one of below to use    
'For exact match
    If Cells(Rw, "A").Value = DelValues(i) Then Del = True
    'For close match
    If InStr(Cells(Rw, "A"), DelValues(i)) > 0 Then Del = True

  Next i
  If Del Then Rows(Rw).Delete
  Del = False
Next Rw

'Delete from Right to Left
Columns(N2).Delete
Columns(N1).Delete

End Sub

If this is too slow, it can be made faster. I kept the logic simple.

[mokhtar]

in my first post i used clear contents from a1 to a10
to delete the row if it contains France, Italy, USA .....try

Sub sbDelete_Rows_IF_Cell_Cntains_String_Text_Value()

Dim LRow As Long
Dim X As Long
Dim Arr As Variant

Arr = Array("France", "Italy", "USA")

LRow = Cells(Rows.Count, "A").End(xlUp).Row

For X = LRow To 1 Step -1
        For i = LBound(Arr) To UBound(Arr)
       
             If Cells(X, "A").Value = Arr(i) Then
                Rows(X).Delete
             End If
        Next i
Next X

End Sub

this code delete to delete the row if it contains France, Italy, USA from row 1 to last used row

[1580067]

Hi all,

Sorry for late reply. Didn't have the chance to log back on since then.

Thanks a lot for your help. I tried to implement those codes but I couldn't make them work. As recommended I attached a file so that it is easier to visualize the problem.

File_Forum.jpg

To answer p45cal yes, it has to be an exact match. The cell can contain only one value i.e only USA or only Germany. If so then, I want excel to cancel the entire row.

Thanks again!

[p45cal]

As recommended I attached a file so that it is easier to visualize the problem.

This is not a workbook file but a picture - it helps visualize but actually adds to the confusion since you've got what appears to be column headers A,B,C etc. which can't be in the sheet's actual columns A,B,C etc.; is that leftmost column actually column A of the sheet? You said as much in your first post, but if not, it's a good reason the codes wouldn't work. A workbook would be much more useful.

[1580067]

Country	Variable 1	Variable 2
France	x	y
Italy	x	y
Germany	x	y
USA	x	y
France	x	y
France	x	y
USA	x	y
Germany	x	y
Japan	x	y

This is not a workbook file but a picture - it helps visualize but actually adds to the confusion since you've got what appears to be column headers A,B,C etc. which can't be in the sheet's actual columns A,B,C etc.; is that leftmost column actually column A of the sheet? You said as much in your first post, but if not, it's a good reason the codes wouldnt work. A workbook would be much more useful.

You are right. I did not manage to link a file. I created this table. Hope this helps.

[p45cal]

I pasted your table into an Excel sheet and ran mokhtar's code from msg#5. It worked well, but didn't try to delete any columns.
I ran SamT's codes. They both worked as far as deleting rows was concerned (you should use the exact match version but it probably doesn't matter which you choose), but when it tries to delete columns, you have to tweak the code to say which columns you want deleted (information which you did not supply) thus:
Columns("D").Delete
Columns("B").Delete
substituting the column you want deleted (remember to delete from the right as SamT says.

[snb]

Sub M_snb()
  for each it in array("A","B","C","D")
     columns(1).replace it,"",1
  next
  columns(1).specialcells(4).entirerow.delete
  Range("F1,K1").EntireColumn.Delete   ' to delete columns F & K in 1 go
End Sub