Conditional row deletion detecting true condition (should delete) but not deleting row..?

**SnyperBunny** · 02-06-2013, 02:31 PM

I am so confused. I have sucessfully used a conditional row deletion before... and practically copied it for this one except now its just not actually deleting the row. It enters the if statement (detecting a true condition), displays the msgbox I put to check if it was even entering the if statement, however it seems to skip right over the 'rows(n).delete' statement. The strange part is that is does seem to be deleting something because my total number of columns that my data is resting on is made smaller. But it isnt deleting the 'n-th' row, so I am very confused.

It is also VERY slow running. it goes through about... 10k rows (deleting the random ones....) in about 2 min. considering that I have 500,000 rows to get through... thats not cool. Is there anyway to speed it up once its actually working? Maybe getting it working will fix the slowness?

The code is to look at column A, pull the month data out of a date (this is what the MID does) and then check it using the IF statement - if it is not a Jan, Feb, Nov, or Dec, the row should be deleted. Also, the 550,000 is not the exact end of my data, I just know it will be under that and over 500,000 rows long.

Sub filter()
    Dim n As Long
    Application.ScreenUpdating = False
    For n = 2 To 550000
        
        If ThisWorkbook.Worksheets(1).Cells(n, 1) <> "" Then
        
            month_check = Mid(ThisWorkbook.Worksheets(1).Cells(n, 1).Value, 6, 2)
            
            If month_check <> "01" And month_check <> "02" And month_check <> "11" And month_check <> "12" Then
                ThisWorkbook.Worksheets(1).Rows(n).Delete
                MsgBox ("month= " & month_check & ", n=" & n)                                           'this is just here so I can see what row it is supposed to be deleting... 
            End If
            'n = n + 1
        End If
        
    Next n
    
    Application.ScreenUpdating = True
    MsgBox ("sorta success")
End Sub

**OllieB** · 02-06-2013, 03:00 PM

Option 1

Public Sub filter_attempt1()
'# declare
    Dim n As Long
    Dim intMonth As Integer
'# freeze application user interface
    Application.ScreenUpdating = False
'# for worksheet 1
     With ThisWorkbook.Worksheets(1)
          For n = .Cells(.Rows.Count, "A").End(xlUp).Row To 2 Step -1
          '# if a value exists in column A
               If LenB(.Cells(n, "A").Value & "") > 0 Then
               '# if the month > 2 and < 11 then delete the row
                    intMonth = CInt(Mid$(.Cells(n, "A").Value, 6, 2))
                    If intMonth > 2 And intMonth < 11 Then
                         .Rows(n).Delete
                    End If
               End If
          Next n
     End With
'# unfreeze
    Application.ScreenUpdating = True
    MsgBox ("sorta success")
End Sub

option 2

Public Sub filter_attempt2()
'# declare
    Dim n As Long
    Dim intMonth As Integer
    Dim objRange As Range
'# freeze application user interface
    Application.ScreenUpdating = False
'# for worksheet 1
     With ThisWorkbook.Worksheets(1)
          For n = .Cells(.Rows.Count, "A").End(xlUp).Row To 2 Step -1
          '# if a value exists in column A
               If LenB(.Cells(n, "A").Value & "") > 0 Then
               '# if the month > 2 and < 11 then delete the row
                    intMonth = CInt(Mid$(.Cells(n, "A").Value, 6, 2))
                    If intMonth > 2 And intMonth < 11 Then
                         If objRange Is Nothing Then
                              Set objRange = .Rows(n)
                         Else
                              Set objRange = Union(objRange, .Rows(n))
                         End If
                    End If
               End If
          Next n
     End With
'# delete
     objRange.Delete
'# unfreeze
    Application.ScreenUpdating = True
    MsgBox ("sorta success")
End Sub

**SnyperBunny** · 02-06-2013, 04:00 PM

Thank you for your help - I think its working.... sort-of... well it WAS anyways.

I tried with the option #1 you posted however it is still very slow, and now it seems to be broken... I broke the loop so see where it was at (I'm impatient...) and when I tried to restart it it just tells me that it reached a break and won't keep running. I see no reason why it would work okay, break and restart fine at least twice, and then all of a sudden decide that it wasnt going to restart.... Any idea why it would be doing this? (I don't know if it makes any difference, but the place it always stops on/highlights (on my original code too) when I break to see what progress has been made, is at the 'End If' of the innermost if-statement. I dont know if this it just because of a super conincidental coincidence, if this line always takes slightly longer to complete and thus has a large chance of breaking on this line, or if the code is actually getting 'Stuck' on this line - possibly why the code is running so slowly?)

Also, aside from the organization (syntax, structure, etc) of the comands being different to what I posted... I dont see any difference. It is still just checking for the IF statement conditions and then .Deleting the row.
Where did I go wrong in my original code? I'd like to know so that I can make a sucessful filter next time I run into problems like this....

edit: aaaand now it has randomly started not-breaking itself and running properly again... still slow though

**OllieB** · 02-06-2013, 04:18 PM

Ps. It may look like it is doing nothing or standing still but it has to process all rows that it has already looked at during the previous run.

**OllieB** · 02-06-2013, 04:15 PM

There is no reason for it not to restart. The biggest difference with your original code is that the new ones loop backwards. Have tried version 2?

**SnyperBunny** · 02-06-2013, 05:09 PM

I suppose so, however I left it running for 30 min and it only moved my rows by not even 2000. I have no idea what is going on with this one - I have done conditional deletion of rows before and it could do about 30,000 is less than 2 min.

on this spreadsheet, it has done a total of aprox 6000 rows in over 2 hours. and it should have simply been deleting all of these. If there were large quantities of rows to check through and not delete then I would understand it taking a bit longer, but starting from the bottom there are probably 10k to delete right off the bat.

any ideas why it is taking so very very long...?

**OllieB** · 02-07-2013, 02:29 AM

It should not take this long! Do you perhaps have formula's in the worksheet or a worksheet_change event routine?

You may want to try adding these two lines at the beginning

Application.EnableEvents = False
Application.Calculation = xlCalculationManual

and these two at the end

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

Conditional row deletion detecting true condition (should delete) but not deleting row..?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Conditional row deletion detecting true condition (should delete) but not deleting row..?

Re: Conditional row deletion detecting true condition (should delete) but not deleting row

Re: Conditional row deletion detecting true condition (should delete) but not deleting row

Re: Conditional row deletion detecting true condition (should delete) but not deleting row

Re: Conditional row deletion detecting true condition (should delete) but not deleting row

Re: Conditional row deletion detecting true condition (should delete) but not deleting row

Re: Conditional row deletion detecting true condition (should delete) but not deleting row

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions