PDA

View Full Version : VBA - Delete Entire Row if Cell contains Text variable



1580067
09-16-2015, 07:42 AM
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
09-16-2015, 01:12 PM
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
09-17-2015, 12:57 PM
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
09-17-2015, 03:11 PM
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
09-19-2015, 11:15 AM
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
09-23-2015, 01:51 AM
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.

14443

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
09-23-2015, 04:04 AM
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
09-23-2015, 05:36 AM
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
09-23-2015, 06:10 AM
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
09-23-2015, 08:26 AM
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