Please speed up my vba code! Deleting rows based on blank cells

[jmelinda]

Hey all,

I found this code on the board and was able to edit it to do what I need it to do but it is realllllly slow. Any suggestions to speed things up?

I have data in Columns A,B, & C. Most of Column C is blank. I want the rows where Column C is blank deleted, without deleting the entire row because I have buttons with macros assigned in Column D, E and F

 Dim rcell As Range
Dim rrange As Range
Dim i as Long
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set rrange = Range("A2:C10000")
        For i = rrange.Rows.Count To 1 Step -1
            If rrange.Cells(i, 3).Value = "" Then
        rrange.Rows(i).Delete
    End If
    Next i

Any help is much appreciated! One way I thought that could speed it up is if the range was just the active range, but the number of rows is variable and I'm not sure how to account for that.

 

[tweedle]

Instead of consuming 10000 rows, consume what is needed

i.e. lr finds the last row(lr) in the sheet with something in column A.

    Dim rcell As Range
    Dim rrange As Range
    Dim i As Long, lr As Long


    Application.ScreenUpdating = False
    Application.DisplayAlerts = False


    lr = Cells(Rows.Count, 1).End(xlUp).Row
    Set rrange = Range("A2:C" & lr)
    rrange.Select
    For i = rrange.Rows.Count To 1 Step -1
        If rrange.Cells(i, 3).Value = "" Then
            'rrange.Rows(i).Delete
            rrange.Cells(i, 1).Resize(1, 3).ClearContents
        End If
    Next i

 

[Rick Rothstein]

This should work for you and be quite a bit faster...

Sub DeleteOnlyABCforBlankCs()
  Dim LastRow As Long
  LastRow = Columns("A:C").Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
  Intersect(Range("C1:C" & LastRow).SpecialCells(xlBlanks).EntireRow, Columns("A:C")).Delete xlShiftUp
End Sub

 

[jmelinda]

Thank you this works perfectly! It is much much faster.