How can i delete or clear cells that exists before zero cell ?

I need to delete cells that found before a cell that contains zero
the attached excel contains an example that I have and I need it like this

Screenshot from 2021-01-25 01-54-30.png

is there any formula that I can use it by drag it down because I have large dataset

Originally Posted by Ochimus

hassan,

Excel formulae will not allow you to "tell" another cell to change. It can only change the cell containing the formula. You will have to use a Macro.

Option Explicit
Dim c As Long, f As Long, m As Long, r As Long

Sub NOZEROES()

'Find last row.
 
    With Sheet1
    f = .Cells(.Rows.Count, "A").End(xlUp).Row
    
    If f = 1 Then Exit Sub
    
'Find last column
    r = .Cells(1, .Columns.Count).End(xlToLeft).Column

Look in each row        
     For m = 2 To f

Look in each column
        For c = 1 To r

If the cell is Zero, then clear it and all cells above it in that column
            If .Cells(m, c) = 0 Then
            .Range(.Cells(1, c), .Cells(m, c)).ClearContents
            End If
            
Move to next column and repeat
        Next

Move to next row and repeat
    Next
    
    End With
    
    End Sub

Ochimus

i tried it for my example and worked but when I tried it in the real data I have it didn't work .. should I press button because I got [ Cannot run the macro… the macro may not be available in this workbook ] and when I run the code from VBA i got no reaction

Thanks for helping .. I'm trying it now but it will take much time to finish because I have 2000 column and row .. can I do it with a formula using if condition and drag it down to all cells for the check to be faster

thanks but i tried it and didn't work

Originally Posted by bebo021999

Does it work for sample file?
With your actual file, could you attach a small piece of file (few rows / columns) showing the error?

Many thanks for your helping i tried the above answer and worked .. Thanks again

Morning Hassan,

The Macro will not take "much time". It will cycle through two thousand rows and however many columns extremely quickly.

If you moved the Macro to a different file that has your "real" data, can you check which sheet number it is on? (not the worksheet name) and change the 'With Sheet1' to whatever. Or replace 'With Sheet1' to 'With Active sheet' and it will work.

Ochimus

Originally Posted by Ochimus

Morning Hassan,

The Macro will not take "much time". It will cycle through two thousand rows and however many columns extremely quickly.

If you moved the Macro to a different file that has your "real" data, can you check which sheet number it is on? (not the worksheet name) and change the 'With Sheet1' to whatever. Or replace 'With Sheet1' to 'With Active sheet' and it will work.

Ochimus

yes I did it already and worked .. thanks