98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA - Deleting a row based on a condition - (text in cell)
Excel VBA - Deleting a row based on a condition - (text in cell)
Resolved · Medium Priority · Version 2003
Tue 16 Sep 2008, 09:34Delegate Richard said...
Richard has attended:
Excel VBA Intro Intermediate course
Excel VBA - Deleting a row based on a condition - (text in cell)
Hi, I have written the code below, (which doesnt work).
I am trying to write a procedure that will go through all cells in the range of B1:B55500 and if the activecell contains the text, (among other text also in that cell) "'A' DIVISION POLICE" - then delete the entire row.
Im not sure of the correct worksheet function or if theres a better VBA function that can be used?
Sub DeleteRow()
Dim BadData As Variant
BadData = Application.WorksheetFunction.Find("'A' DIVISION POLICE", 1)
Range("B1:B55500").Select
If BadData = True Then
Rows(ActiveCell.Row).Select
Selection.Delete Shift:=xlUp
End If
End Sub
Any help please?
Wed 17 Sep 2008, 12:29
Edited on Wed 17 Sep 2008, 12:31Trainer Laura said...
RE: Excel VBA - Deleting a row based on a condition - (text in c
Rather than using find I would check each cell in the column and delete the row if required. The check to see if a row is to be deleted uses Like in an If statement and uses wildcard character *.
Sub RemoveLines()
Dim Counter As Long
' Start at B1
Range("B1").Select
For Counter = 1 To 55500
If ActiveCell.Value Like "*'A' DIVISION POLICE*" Then
ActiveCell.EntireRow.Delete ' Delete Row
Else
ActiveCell.Offset(1, 0).Select ' Move down a row
End If
Next Counter
End Sub
Let me know if you need further explanation.
Laura GB
Wed 17 Sep 2008, 18:53Delegate Richard said...
RE: Excel VBA - Deleting a row based on a condition - (text in c
Hi Laura,
thank you so much for your reply - incredibly helpful worked perfectly.
A few Q's - hope you dont mind.
Simple thing, but using Counter - vba doesnt seem to recognise this word - the code works fine, but if you type If or Then etc, vba will capitalise the first letter, but not with Counter, yet vba obvioulsy understands it, or could you just use any word instead of Counter, its the For part and the = 1 To 55500 part it recognises?
Also, in the mean time I tried to do this another way, and came up with the following code - which also doesnt work,
Sub DeleteRow2()
Dim Counter As Long
Range("B1").Select
For Counter = 1 To 61000
If InStr(ActiveCell.Value, "'A' DIVISION") > 0 Then
Rows(ActiveCell.Row).Select
Selection.Delete Shift:=xlUp
End If
Next Counter
End Sub
Your reply & code is perfect, but I'd like to know what I've done wrong with the above code, im presuming its my use of the Instr syntax, if possible I'd like to know how I should have written it usng Instr ?
Thanks again Laura, do appreciate your help.
Wed 17 Sep 2008, 21:13Trainer Laura said...
RE: Excel VBA - Deleting a row based on a condition - (text in c
Hi Richard
I'm glad it works.
Counter is a variable so the name you chose in the Dim line could be anything as long as it follows the rules of no spaces, only letters and number or _
In your version which should work fine you missed except you missed out one part.
Before the End If add the lines
Else
Activecell.Offset(1,0).Select
So that you move down a line to check the next line.
Laura
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Use the Format Painter to copy formatting more than once in ExcelThe format painter tool provides a quick and easy way to copy formatting from one cell to another in Word. |
