Previous Topic Next Topic

VB Excel 2010 - Loop until can't find

Solved/Closed
whitepanther - 6 Jun 2011 à 23:55
 whitepanther - 26 Oct 2011 à 20:33
Hello,

We are trying to manipulate data from a very old DOS program. We have a lengthy macro that works well until the end where it seaches for some specific words and loops deleting the rows with these words in it until it can't find anything then comes up with an error and stops. What we're trying to do is get the script to keep running until it doesn't find anymore instances of the search words and then carries on to the next part of the script that we want to write.

Here's the coding loop where it gets stuck:


Dim lCount As Long
Dim lNum As Long


lCount = 0
lNum = 11

Application.ScreenUpdating = False


Do While lNum > 10

Columns("B:B").Select
Selection.Find(What:="CE - Parish Currently", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
'
ActiveCell.Select
ActiveCell.Offset(-1, 0).Select
Selection.EntireRow.Delete
Selection.EntireRow.Delete
Selection.EntireRow.Delete
Selection.EntireRow.Delete
Selection.EntireRow.Delete
Selection.EntireRow.Delete
Selection.EntireRow.Delete
Selection.EntireRow.Delete
Selection.EntireRow.Delete

Loop

We want to do this same function in a few other scripts we've been writing and my boss and I are both novices at this so any help you might be able to offer is much appreciated!



Related:

1 response

Réponse 1 / 1
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
7 Jun 2011 à 02:31
The code says
Do While lNum > 10

So when does lNum ever becomes less than or equal to 10 to terminate the loop



Since you have not given all info, this I am thinking is the most logical solution 

   Do While True
      Set Cell = Columns(2).Find(What:="CE - Parish Currently", _
                                 After:=Cells(1, 2), _
                                 LookIn:=xlFormulas, _
                                 LookAt:=xlPart, _
                                 SearchOrder:=xlByRows, _
                                 SearchDirection:=xlNext, _
                                 MatchCase:=False, _
                                 SearchFormat:=False)
   
      If Cell Is Nothing Then Exit Do
      Cell.EntireRow.Delete
   Loop
whitepanther
7 Jun 2011 à 17:32
It doesn't! I was wondering about that... my boss copied it straight off the net I think - you can tell we're complete novices!

This works great except that I forgot to mention that we need it to delete more than just the row with the words in it. It needs to delete the row with the words and the 8 rows directly below it as well - so 9 rows in total. Been trying to play with what you've given me but can't seem to figure out a way to get it to do that second part as well....
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
7 Jun 2011 à 17:48
Change this line
Cell.EntireRow.Delete

to

Range(Cell, Cells(Cell.Row + 8, Cell.Column)).EntireRow.Delete
whitepanther
7 Jun 2011 à 18:24
Thank you so much - you're an absolute star! :)
whitepanther
8 Jun 2011 à 19:29
Hello again,

Just trying to use this loop in another macro but it doesn't seem to like the command I've given it. It's almost exactly the same except the search words and the last command.

Rather than deleting a specific number of rows beneath the specified search words, because the amount of info varies we want it to select the entire section of info underneath (we used the shortcut Shift - End - Down arrow to make the selection). This is what I've tried but hasn't worked.

Do While True
Set cell = Columns(2).Find(What:="other dues", _
After:=Cells(1, 2), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If cell Is Nothing Then Exit Do
Range(Selection, Selection.End(xlDown)).EntireRow.Delete

Are you able to shed some light?
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
9 Jun 2011 à 03:49
Instead of
Range(Selection, Selection.End(xlDown)).EntireRow.Delete

try
Range(cell , cell.End(xlDown)).EntireRow.Delete

Similar discussions

Product Key for Publisher 2010
SeriDahari -
jack4rall -

1 response