Delete Row Loop Optimization VBA

Question

The code below was written as part of a random sample generator. Based on the total number of occurences, it calculates the sample size and reduces the population to the sample size. Right now I'm doing this by deleting the difference between the total and the sample size. I'd like to see if there's a better way of going about this. I'm getting a sample of each value in a user-defined column and working with a large data set, so the sampling ends up taking a few minutes.

Is there way to delete the number of rows I need to all at once instead of having to do them one at a time as seen in the loop below, or a better way to go about this altogether? Thank you!

x = (Population - SampleSize)

    If Population > SampleSize Then
        Do Until x = 0
            Workbooks(SourceBook).Sheets(SampleSheet).Columns(StratCol) _
            .Find(What:=SubPop, After:=Cells(SampRows, StratCol), LookIn:=xlValues, _
                SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).EntireRow.Delete

        x = x - 1

        Loop

    End If

yes, there is, but only by adding a formula column as in [How to delete multiple rows without a loop in Excel VBA](http://stackoverflow.com/questions/15431801/how-to-delete-multiple-rows-without-a-loop-in-excel-vba) - see my answer for a possible solution — Our Man in Bananas, Mar 19 '13 at 09:45
@ForrestA Kindly refer to similar thread. http://stackoverflow.com/questions/15375054/vba-macro-to-delete-rows-quickly — , Mar 19 '13 at 12:03
yes, AUtoFilter/Advanced Filter is another option **without a loop** where SpecialCells doesn't have to be used — Our Man in Bananas, Mar 19 '13 at 12:26
To clarify, I'm mainly looking for this to achieve the desired result quicker. Whether or not this is a loop doesn't matter to me, though I do appreciate the diversity in approaches to this problem. Also, keep in mind that this is deleting a specific portion of the rows with matching criteria and not all of them. Thank you very much for the suggestions! When I get a chance to give them a spin I'll let you know which one works best for me. — ForrestA, Mar 19 '13 at 15:34

score 1 · Accepted Answer · answered Mar 19 '13 at 05:48

You can build a range that contains multiple non-contiguous rows and then delete all of them at once. This will probably speed things up a bit.

x = (Population - SampleSize)

dim MyRange as Range

If Population > SampleSize Then
    Do Until x = 0
        if MyRange Is Nothing Then
            Set MyRange = Workbooks(SourceBook).Sheets(SampleSheet).Columns(StratCol) _
                .Find(What:=SubPop, After:=Cells(SampRows, StratCol), LookIn:=xlValues, _
                SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).EntireRow
        Else
            Set MyRange = Application.Union(MyRange, Workbooks(SourceBook).Sheets(SampleSheet).Columns(StratCol) _
                .Find(What:=SubPop, After:=Cells(SampRows, StratCol), LookIn:=xlValues, _
                SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).EntireRow)
        End If
        x = x - 1
    Loop
    MyRange.Delete
End If

score 0 · Answer 2 · edited May 23 '17 at 12:07

0

for this solution a loop can be avoided.

Just add an extra column creating an error which can be found using SpecialCells as below:

Range("IA1:IA" & Range("A65536").End(xlUp).Row).Formula = "=IF(A:A = """ & SubPop & """,NA(),"""") "

then you use SpecialCells to identify the rows matching SupPop, and delete the entire row!

Range("IA1:IA" & Range("A65536").end(xlup).row).specialcells(xlCellTypeFormulas, xlErrors).EntireRow.Delete shift:=xlup

Finally, clean up/delete the extra formula colummn we added at the beginning:

Range("IA1:IA" & Range("A65536").end(xlup).row).clear

Here is a proper explanation of this technique: How to delete multiple rows without a loop in Excel VBA

I hope that helps to get you started

and here is the above code all in one function as eagerly requested:

Sub deleteRows(ByVal strSubPop As String)
' pass in the string to match (SubPop)
'
    Range("IA1:IA" & Range("A65536").End(xlUp).Row).Formula = "=IF(A:A = """ & strSubPop & """,NA(),"""") "
'    
    ' for debugging and testing just select the rows where a match is found
    Range("IA1:IA" & Range("A65536").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Select
 '   
    ' when ready, UNCOMMENT the below line
   ' Selection.Delete shift:=xlUp
  '  
    ' after testing just use this line to select the rows and delete in one step:
    ' Range("IA1:IA" & Range("A65536").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete shift:=xlUp
   '  
    ' now clean up Column IA
    Range("IA1:IA" & Range("A65536").End(xlUp).Row).Clear
    Range("A1").Select
'
End Sub

so what is that code doing?

First your requirement is to delete rows matching SubPop

The first line adds a formula in Column IA that matches SubPop found in Column A
The next line finds all those rows then deletes them
the final line cleans up

Philip

edited May 23 '17 at 12:07

Community

1
1

answered Mar 19 '13 at 09:41

Our Man in Bananas

5,809
21
91
148

I guess most of ppl know SpecialCells. Kindly use it against a value and show me if it works. Waiting for your reply eagerly. – Mar 19 '13 at 10:02
the above code uses the value SubPop. If found in column A then the formula returns an NA# error code in the cells in column IA. SpecialCells searches in Column IA for errors (NA#) then the range command returns the entire row that takes the Delet method! – Our Man in Bananas Mar 19 '13 at 10:06
@user2063626 sorry there was a bug in the line adding a formula to column IA, this has now been fixed! – Our Man in Bananas Mar 19 '13 at 10:23
@ForrestA though i would work its a wrong way of doing the above task. – Mar 19 '13 at 10:50
@user2063626 the OP asked for a solution **avoiding** a loop. It's not a workaround, it's a possible solution. – Our Man in Bananas Mar 19 '13 at 11:31
what if the range A:A has a text " mango" and strSubPop ="mango" what would be the result ?? – Mar 19 '13 at 11:35
well, you could always try it, but in your example, then all the matching rows are selected and deleted, **which is what the user wants** – Our Man in Bananas Mar 19 '13 at 12:20
of course, the criteria are determined by the OP, and the Column A:A **is just an example** - this needs to be *modified* by the OP – Our Man in Bananas Mar 19 '13 at 12:23
I understand these are examples but i just pointed a situation where the logic can fail and may lead to misleading data. – Mar 19 '13 at 17:34

score -1 · Answer 3 · answered Mar 19 '13 at 07:05

For this situation loop cannot be avoided. Try below code.

 Dim rng As Range
    x = (Population - SampleSize)

    If Population > SampleSize Then
        Do Until x = 0

            With Workbooks(SourceBook).Sheets(SampleSheet)
                Set rng = .Columns(StratCol).Find(What:=SubPop, After:=Cells(SampRows, StratCol))
                If Not rng Is Nothing Then
                    rng.EntireRow.Delete
                End If
            End With

            x = x - 1
        Loop
    End If

loop can be avoided, see my answer – Our Man in Bananas Mar 19 '13 at 09:42 — Our Man in Bananas, Mar 19 '13 at 09:42

Delete Row Loop Optimization VBA

3 Answers3