CODEWITHSUNDEEP
vbaexcel
user1717622
user1717622

Reputation: 323

Simple For Loop taking ages

I have this simple loop designed to look at a block of data in excel: it firstly identifies how many rows it extends down - this defines the number of iterations then on each row N, looks to see if the cell(N,B) is empty - if so deletes that row.

This seems to not work, also it takes ages! I need something that does this very quickly.

Any thoughts would be aprreciated

Sub PREBILLvariant2()

        Dim N As Long

 For N = 1 To Worksheets("EMEA input").Cells(Rows.Count, "A").End(xlUp).Row

If InStr(Cells(N, "B").Value, "") > 0 Then Worksheets("EMEA input").Cells(N, "B").EntireRow.Delete


    Next N

    End Sub

Upvotes: 0

Views: 96

Answers (3)

Martin Carlsson
Martin Carlsson

Reputation: 471

This is a more standart approach:

It loops over every row with content in column A and delete every row that has an empty cell in column C

Sub subDeleteRows()
    Dim lngRow As Long: lngRow = 1

    subSpeedUp True

    Do Until IsEmpty(Sheets("EMEA input").Cells(lngRow, 1))
        If IsEmpty(Sheets("EMEA input").Cells(lngRow, 2)) Then
            Sheets("EMEA input").Cells(lngRow, 2).EntireRow.Delete
        Else
            lngRow = lngRow + 1
        End If
    Loop

    subSpeedUp False
End Sub

Sub subSpeedUp(startStop As Boolean)
    If startStop Then
        Application.ScreenUpdating = False
        Application.DisplayStatusBar = False
        Application.Calculation = xlCalculationManual
        Application.EnableEvents = False
        ActiveSheet.DisplayPageBreaks = False
    Else
        Application.ScreenUpdating = True
        Application.DisplayStatusBar = True
        Application.Calculation = xlCalculationAutomatic
        Application.EnableEvents = True
    End If
End Sub

If you got the time, you should read this article: http://msdn.microsoft.com/en-us/library/office/ff726673.aspx

Upvotes: -2

Joseph
Joseph

Reputation: 5160

Here's another method. I always found Filtering to be the fastest way to do these things.

Public Sub filterThenDelete()
    Application.ScreenUpdating = False

    Dim r As Excel.Range
    Set r = Sheets("EMEA input").UsedRange

    r.AutoFilter Field:=2, Criteria1:=""

    Dim deleteRange As Excel.Range
    Set deleteRange = r.Offset(1, 0).Resize(r.Rows.Count - 1, r.Columns.Count).Cells.SpecialCells(xlCellTypeVisible)

    deleteRange.EntireRow.Delete

    Sheets("EMEA input").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

Just an FYI, for the AutoFilter, Field:=2 is saying "Apply the filter found in Criteria1 to column 2 in the range selected." Or simply "Filter column B for blank values."

Upvotes: 3

Andy G
Andy G

Reputation: 19367

Something like this:

Sub PREBILLvariant3()
    Dim ws As Worksheet
    Dim lRows As Long, N As Long
    Dim rngToDelete As Range

    Application.ScreenUpdating = False
    Set ws = Worksheets("EMEA input")
    lRows = ws.Cells(Rows.Count, "A").End(xlUp).Row
    For N = 1 To lRows
        If ws.Cells(N, "B").Value <> "" Then
            If rngToDelete Is Nothing Then
                Set rngToDelete = ws.Cells(N, "B")
            Else
                Set rngToDelete = Union(rngToDelete, ws.Cells(N, "B"))
            End If
        End If
    Next N
    rngToDelete.EntireRow.Delete
    Application.ScreenUpdating = True
    Set ws = Nothing
End Sub

This collects, as a Range all the cells in B that are not empty (<> "") and deletes the rows in one go after the loop.

Checking for not empty (<> "") or Len() > 0 is better IMO than using InStr(), as you are not looking for specific text.

Upvotes: 2

Related Questions