faster way to delete cells that equal 0 ?

[mthompso]

My current code is:

For each rngCell in Range("MyRange")
     If rngCell.Value = 0 Then rngCell.ClearContents
Next

It takes over a minute for this VBA macro to evaluate each cell.

Is there a faster way to accomplish this task?

Background: The worksheet is protected. If it wasn't protected, I would simply change the number format so that the zeros don't even appear.

Thanks for your help.

 

[njimack]

Have you tried using autofilter to only display those cells equal to zero?
You could then use the following code to delete them
SpecialCells(xlCellTypeVisible).ClearContents

 

[bjurney]

Cant you just unprotect the sheet, change the format and then protect it again?

I tried your code on a protected worksheet, and it doesnt work because its protected. Is there something in your code that unprotects the sheet? or is the "MyRange" unlocked?


You can try something like this:

    Range("MyRange").Replace What:="0", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

 

[mthompso]

Have you tried using autofilter to only display those cells equal to zero?
You could then use the following code to delete them
SpecialCells(xlCellTypeVisible).ClearContents

There are approximately 50 columns of data, so I would have to cycle through each column and run this code for each column using a "For each" loop.

Anybody know which method is quicker...
1) Evaluating each cell in the range using a "For each" loop and clearing the cell contents if it equals zero, or
2) Running autofilter 50 times using a "For each" loop and clearing the contents if it equals zero

Thanks

 

[mthompso]

Cant you just unprotect the sheet, change the format and then protect it again?

I tried your code on a protected worksheet, and it doesnt work because its protected. Is there something in your code that unprotects the sheet? or is the "MyRange" unlocked?


You can try something like this:

    Range("MyRange").Replace What:="0", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

The auditors in a different country have the password. This not an option. They are very protective of the integrity of the worksheets and aren't willing to unprotect the worksheet just to make my life easier.

The individual cells being changed aren't locked.

Thanks

 

[mole999]

i would change the original code to

    Application.ScreenUpdating = False
    Dim LastRow
    Dim c
    LastRow = Range("B65536").End(xlUp).Row
    Range("B6:EK" & LastRow).Select ' set you column width here

    Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _
                      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                      ReplaceFormat:=False

so you only deal with actual rows and columns, rather the whole shebang

 

[njimack]

There are approximately 50 columns of data, so I would have to cycle through each column and run this code for each column using a "For each" loop.

Anybody know which method is quicker...
1) Evaluating each cell in the range using a "For each" loop and clearing the cell contents if it equals zero, or
2) Running autofilter 50 times using a "For each" loop and clearing the contents if it equals zero

Thanks

No you wouldn't. Just filter each column for zero values, then run the line of code I posted on the entire range. Somethign like this:

Sub Delete_Zero()
Dim Last_Row As Long
Dim Last_Col As Long
Dim i As Integer

With Sheets("Sheet1")
    Last_Row = .Range("A" & Rows.Count).End(xlUp).Row
    Last_Col = .Range("A1").End(xlToRight).Column
    
    For i = 1 To Last_Col
    
        Range(.Cells(1, 1), .Cells(Last_Row, Last_Col)).AutoFilter Field:=i, Criteria1:=0
        
        On Error Resume Next
        Range(.Cells(2, i), .Cells(Last_Row, i)).SpecialCells(xlCellTypeVisible).ClearContents

    Next i
.ShowAllData
End With

End Sub