Clearing cell contents with macro

[kool_mas]

I have formula in a range B11:HR2035.

I have a simple macro to paste special the values in the range. Many of the cells are blank, so when the paste special is done, even the blank cells are counting towards the size of the file.

The size of the file is 7 Mb, and when I manually clear the contents of the blank cells (selecting and deleting the cells), the size of the file becomes 1 MB.

I tried writing a macro to clear the contents based on some condition :

Worksheets("Sheet3").Activate

    For i = 1 To 225
    
        If Cells(10, i + 1) = 0 Or Cells(9, i + 1) = 0 Then
        Worksheets("Sheet3").Range(Cells(11, i + 1), Cells(2035, i + 1)).Select
         Selection.ClearContents
        End If
    
    Next i

This takes a long time to execute (more than 3 minutes)

Is there a better more efficient way to clear the cell contents, so that the size of file becomes more manageable?

Thanks.

 

[texasalynn]

not sure why it would take that long, made one adjustment. It isn't necessary to select as that will slow down the macro.

Sub cleanCell()
    For i = 1 To 225
        If Cells(10, i + 1) = 0 Or Cells(9, i + 1) = 0 Then
        Worksheets("Sheet3").Range(Cells(11, i + 1), Cells(2035, i + 1)).ClearContents
        End If
    
    Next i
    MsgBox "Done"
End Sub

 

[Jon von der Heyden]

I think you can avoid a lengthy loop like this:

Sub Macro1()

Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Clear

End Sub

Which you can apply to a specific range... And assumes cells are blank and do not contain zeros...

 

[kool_mas]

Thanks Jon von der Heyden. It doesnt seem to work.

Selection.SpecialCells(xlCellTypeBlanks).Select does not select the blank cells. If i manually delete the contents, and run your macro, it selects the blank cells. For some reason the cells are not getting identified as blank.

texasalynn, I will test your suggestions to see if it reduces time. Thanks.