More effecient code than using a Loop ?

[Swaroon]

Hi,
I have been using the code below, has been working ok but now I have more data so it takes considerably longer to run through.
Code loops down column B and if cell on same row in column A = 0 then deletes zero
Is there a more efficient way of doing this ?


thanks Steve


Range("B5").Select

Do Until ActiveCell = ""
If ActiveCell = 0 Then
ActiveCell.Offset(0, -1).Clear
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop

 

[MrKowz]

Give this a go - this looks in column B for cells which equal 0 and then clears the contents from the corresponding row in column A.

Public Sub Swaroon()
Dim rng     As Range, _
    rng1    As String
    
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

With Range("B:B")
    Set rng = .Find(0, LookIn:=xlValues, LookAt:=xlWhole)
    If Not rng Is Nothing Then
        rng1 = rng.Address
        Do
            rng.Offset(0, -1).ClearContents
            Set rng = .FindNext(rng)
        Loop While Not rng Is Nothing And rng.Address <> rng1
    End If
End With

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

 

[JackDanIce]

Maybe filter instead of looping? Replace all of code above with below then try:

Dim LastRow As Long
    Dim x       As Long
    
    LastRow = Application.Max(5, Cells(Rows.Count, 2).End(xlUp).row) - 4
    
    Application.ScreenUpdating = False
        
    With Cells(1, 4).Resize(LastRow, 2)
        .AutoFilter
        .AutoFilter Field:=2, Criteria1:=0
        .Offset(1).Resize(1, LastRow - 1).ClearContents
    End With
    ActiveSheet.AutoFilterMode = False
    
    Application.ScreenUpdating = True

 

[MrTeeny]

It's generally more efficient to stick your data into an array, loop thru it amending any data in the array and then dump it back to the screen in one pass, also worth switching screenupdating off , calculation to manual whilst the code runs if you're continually dumping data to the cells one at a time.

 

[MrTeeny]

Sub LoopB()
Dim i As Long
Dim varray As Variant
Application.Calculation = xlCalculateManual
Application.ScreenUpdating = False
On Error GoTo xit:


varray = Range("A1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Value


For i = 1 To UBound(varray, 1)
If varray(i, 1) = 0 Then varray(i, 1) = ""
Next




Range("A1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Value = varray ' dump back to screen




xit:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

You can always stick a timer in to compare the various methods see which is best for you

just by adding the lines

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">[COLOR=#303336][FONT=inherit]lStart [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Timer
[/FONT][/COLOR]

'your looping code in here and the time will be displayed in the intermediate window

[COLOR=#303336][FONT=inherit]
    lEnd [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Timer

    Debug[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Print [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Duration = "[/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]lEnd [/FONT][/COLOR][COLOR=#303336][FONT=inherit]-[/FONT][/COLOR][COLOR=#303336][FONT=inherit] lStart[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]" seconds"[/FONT][/COLOR]</code>

 

[JackDanIce]

For i = 1 To UBound(varray, 1)
If varray(i, 1) = 0 Then varray(i, 1) = ""
Next

Should this part be:

For i = 1 To UBound(varray, 1)
    If varray(i, 2) = 0 Then varray(i, 1) = ""
Next i

OP appears to be testing column B for values of 0, rather than A?

 

[Swaroon]

Thanks everyone, great information as usual !

 

[Rick Rothstein]

Here is another macro you can try...

Sub ClearColumnAifColumnBequals0()
  Columns("B").Replace 0, "#N/A", xlWhole
  On Error Resume Next
  With Columns("B").SpecialCells(xlConstants, xlErrors)
    .Offset(, -1).ClearContents
    .Value = 0
  End With
  On Error GoTo 0
End Sub

 

[MrTeeny]

Nice coding Rick, I'm only starting out with vBA any chance you could explain the steps your code's doing as I can imagine it'd come in useful for replacing a lot of loops i use in my personal code.

Thanks

 

[codeliftsleep]

It's generally more efficient to stick your data into an array, loop thru it amending any data in the array and then dump it back to the screen in one pass, also worth switching screenupdating off , calculation to manual whilst the code runs if you're continually dumping data to the cells one at a time.

Yeah, variant arrays are lightning fast compared to working on the sheet itself...you can basically write once or twice from the array to a whole range of cells versus trying write each time you make a change