VBA conditional delete in column range is too slow

[Ria]

Hi all:

My problem looks simple, but some how it takes considerable time to delete range of columns based on condition. Here is situation:
I have data in columns H, I, J & K. from row 12 to 50. In column I is input and other columns are calculated fields, I keep adding/deleting data in column I. I want VBA macro, if any cell in column I12:I50 is empty then delete data from relevant cells from other Columns (H12:H50, J12:J50 & K12:K50). So far I have VBA code that works but it takes considerable time to do the process. I am looking better/faster solution. Using excel 2003.
Here is my code:

Sub Condition_Delete() 
Dim LR As Long
Set sh = ActiveSheet
  Application.ScreenUpdating = False
  For i = 14 To 50 '===LR
  If Cells(i, "I") = "" Then
  sh.Cells(i, "H").ClearContents
  sh.Cells(i, "J").ClearContents
  sh.Cells(i, "K").ClearContents
  End If
  Next i
  Application.ScreenUpdating = True
End Sub

Any help with faster solution is appreciated. Right now I can delete manually way faster than vba macro.

Thanks,

Ria

 

[Luke M]

Try this one. Should go a lot faster as you're not reading from the worksheet lots of times.

Sub Condition_Delete()
Dim blankCells As Range
Dim c As Range

Application.ScreenUpdating = False

'Any blank cells? We skip an error in case there are none
On Error Resume Next
Set blankCells = Range("I14:I50").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not blankCells Is Nothing Then
    'Loop through each cell in the blankCells range
    For Each c In blankCells
        'Clears the range of H:K. Since col I is already blank, this works
        'and we did it in only one line of code
        c.Offset(, -1).Resize(1, 4).ClearContents
    Next c
End If

Application.ScreenUpdating = True
End Sub

 

[NARAYANK991]

Hi Luke ,

I just tried out your code after removing the outer IF statement , and it still works ! I stepped through the code , and it enters the For ... Next loop the first time , but it does nothing , since c is nothing !

Sub Condition_Delete()
    Dim blankCells As Range
    Dim c As Range

    Application.ScreenUpdating = False

    On Error Resume Next
    Set blankCells = Range("I14:I50").SpecialCells(xlCellTypeBlanks)

    For Each c In blankCells
        c.Offset(, -1).Resize(1, 4).ClearContents
    Next c

    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

Narayan

P.S. The OP mentions I12 everywhere , but within the code uses I14 ; that is a second mystery !

 

[Ria]

Hi Luke ,

I just tried out your code after removing the outer IF statement , and it still works ! I stepped through the code , and it enters the For ... Next loop the first time , but it does nothing , since c is nothing !

Sub Condition_Delete()
    Dim blankCells As Range
    Dim c As Range

    Application.ScreenUpdating = False

    On Error Resume Next
    Set blankCells = Range("I14:I50").SpecialCells(xlCellTypeBlanks)

    For Each c In blankCells
        c.Offset(, -1).Resize(1, 4).ClearContents
    Next c

    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

Narayan

P.S. The OP mentions I12 everywhere , but within the code uses I14 ; that is a second mystery !

Hi Narayan & Luke:

Both of your replies are appreciated, however Narayan's solution looks bit faster than mine and luke's solution. I can see the difference but still slow, not sure why.
Yes, there is discrepancy in code and thread question, both should be read as I14 but I12 or I14 should not make code slower.

Again thanks for your time.

Ria

 

[NARAYANK991]

Hi Riaz ,

Can you specify over how many rows you are running this code ? It cannot be 50 rows , since over 50 rows I cannot imagine it taking more than a second.

Specify the number of rows , and the approximate time it is taking.

Narayan

 

[Luke M]

Hi Narayan,
True, you can take out the If statement...but you had to leave the "On Error Resume Next" statement in affect for longer. I tend to try to avoid this, as it can have...unusual results. I'll admit, it comes down to code/usert preference as to which method to use. I'd hope that my one line of code was not slowing things down too much.

 

[Ria]

Hi Riaz ,

Can you specify over how many rows you are running this code ? It cannot be 50 rows , since over 50 rows I cannot imagine it taking more than a second.

Specify the number of rows , and the approximate time it is taking.

Narayan

Hi Narayan:

My current data range is I14:I33, it takes about 8 to 10 seconds (20 rows). As range grows to I50 but some do not have data at all currently and if I run till I50 then takes about 15 seconds and looks like stuck in loop or went in sleep mode.

Deleting manually looks best option right now.

Great thanks for taking time.

Ria

 

[Luke M]

MY guess then would be that the problem is with formulas in the workbook, using volatile functions or something that is causing massive recalculations when you're deleting cells. You could turn calculations off while running the macro, but that would be treating the symptom, rather than the cause. Does your workbook have a lot of formula calculations going on?

 

[Ria]

MY guess then would be that the problem is with formulas in the workbook, using volatile functions or something that is causing massive recalculations when you're deleting cells. You could turn calculations off while running the macro, but that would be treating the symptom, rather than the cause. Does your workbook have a lot of formula calculations going on?

Hi Luke:

Only column J has formula and I tried to remove formula (entering just values) then ran macro but results are same. Even during delete formula also get delete.

All in all thanks for looking at it in great detail. I will leave it for now and will go with manual delete.

Thanks,

Ria