-
Nov 15th, 2012, 03:28 PM
#1
Thread Starter
Junior Member
[RESOLVED] VBA Find search but skip a result
Hello I am looking to search my spread sheet and delete anything that says "Microsoft" except for "Microsoft Standard 2010".
The problem I am having is it's not skipping to the next instance when it reaches Microsoft Standard 2010. Any suggestions?
Code:
Set FoundCell = Range("E:E").Find(what:="Microsoft")
Do Until FoundCell Is Nothing
If FoundCell.Value <> "Microsoft Standard 2010" Then
FoundCell.EntireRow.Delete
End If
Set FoundCell = Range("E:E").FindNext
Loop
-
Nov 15th, 2012, 03:42 PM
#2
Re: VBA Find search but skip a result
you should put foundcell as the after argument for findnext, if the row is not deleted
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Nov 15th, 2012, 04:09 PM
#3
Thread Starter
Junior Member
Re: VBA Find search but skip a result
Thanks for the reply but with my original code it still deletes Microsoft Standard 2010, it doesn't just not skip to the next instance. My mistake on the original post.
-
Nov 15th, 2012, 07:27 PM
#4
Re: VBA Find search but skip a result
If you have a large amount of data, this won't be the fastest way (making the Find...Findnext work WOULD be), but you could do something like:
Code:
Sub delRows()
Dim i As Long
Dim lastRow As Long
lastRow = Range("e" & Rows.Count).End(xlUp).Row
For i = lastRow To 2 Step -1
If Range("e" & i).Value <> "microsoft standard 2010" And Left(Range("e" & i).Value, 9) = "microsoft" Then
Range("a" & i).EntireRow.Delete
End If
Next i
End Sub
-
Nov 16th, 2012, 03:19 AM
#5
Re: VBA Find search but skip a result
i would suggest you check if there are any additional spaces or other hidden characters, that makes the value not = to the "Microsoft Standard 2010", you could also try the text property of the cell, or use instr rather than =
if you want to post a sample workbook that demonstrates the problem, i am sure someone can assist you
note i can only open .xls, so if you are using a later version of excel, use saveAs .xls, and zip before attaching
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Nov 16th, 2012, 09:15 AM
#6
Thread Starter
Junior Member
Re: VBA Find search but skip a result
Thanks for the help I ended up tweaking vbfbryce's code, but the reason it kept deleting office is because I was looking for "Microsoft Standard 2010" instead of the correct "Microsoft Office Standard 2010" so once I fixed that everything was smooth sailing. Thanks for all the help guys.
JO
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|