Loop through all worksheets.

[JapanDave]

I keep getting a error on the below line. I have been trying to figure this for a while and no longer posses the ability to think out side the box.

The macro looks for cells with "0" in them and deletes the zero.

I get errors on these two lines,

For Each ws In wb
            For Each cel In .UsedRange 'Range("A1:N" & LR)

Sub clear()
   Dim cel     As Range
   Dim LR      As Long
   Dim ws      As Worksheet
   Dim wb      As Workbook
   
        Set ws = ActiveSheet
        Set wb = ActiveWorkbook
        
        LR = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
        
    With ActiveSheet
      'On Error Resume Next
           Set cel = ActiveCell
         For Each ws In wb
            For Each cel In .UsedRange 'Range("A1:N" & LR)
              If cel = "0" Then cel.ClearContents
            Next cel
         Next ws
    End With


End Sub

[JBeaucaire]

"0" is text string. The number zero does not have quotes around it.

This is how I would write that:

Sub clear()
Dim zFIND As Range, ws As Worksheet
   
On Error Resume Next

For Each ws In ActiveWorkbook.Worksheets
    With ws
        Set zFIND = .Cells.Find(0, LookIn:=xlValues, LookAt:=xlWhole)
        If Not zFIND Is Nothing Then
            Do
                zFIND = ""
                Set zFIND = Nothing
                Set zFIND = .Cells.Find(0, LookIn:=xlValues, LookAt:=xlWhole)
            Loop Until zFIND Is Nothing
        End If
    End With
Next ws

End Sub

[JieJenn]

You meant for each WS in worksheets? And you meant numeric 0 not text value 0 right?

[JapanDave]

Thanks Jerry, that did the trick. I am gathering find is quicker than looping through each cell?

[JBeaucaire]

Yes, the FIND method jumps directly to the next cell with the searched value, much more efficient than checking each cell row by row yourself. Eventually there are no more values that match (since you're removing them) and the zFIND range equals nothing. At that point the macro exits.


Note, to implement this method in a macro where the values are NOT being removed as you jump through them all, you'll need to set a secondary range to remember the first one found so that when you come back around to where you started, you'll know and can exit. Here's the same macro again without the delete value command, so you can see what I mean.

Option Explicit

Sub clear()
Dim zFIND As Range, zFIRST As Range, ws As Worksheet
   
On Error Resume Next

For Each ws In ActiveWorkbook.Worksheets
    With ws
        Set zFIND = .Cells.Find(0, LookIn:=xlValues, LookAt:=xlWhole)
        If Not zFIND Is Nothing Then
            Set zFIRST = zFIND
            Do
                zFIND.EntireRow.Interior.ColorIndex = 8
                Set zFIND = .Cells.FindNext(zFIND)
            Loop Until zFIND.Address = zFIRST.Address
        End If
    End With
Next ws

End Sub

[JapanDave]

Thanks Jerry,
That make things a whole lot clearer.