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

**hassan7** · 01-24-2021, 07:55 PM

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

**Ochimus** · 01-24-2021, 08:31 PM

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.

Please Login or Register  to view this content.

Ochimus

**hassan7** · 01-24-2021, 09:07 PM

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

**hassan7** · 01-24-2021, 10:00 PM

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.

Please Login or Register  to view this content.

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

**bebo021999** · 01-24-2021, 10:31 PM

Try to list in other available range, then copy/paste value back to the original range.

=IFERROR(IF(ROW($A1)<=MATCH(0,A:A,0),"",A1),A1)

Drag down and accross

**hassan7** · 01-24-2021, 10:46 PM

thanks but i tried it and didn't work

**bebo021999** · 01-24-2021, 11:13 PM

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

**Ochimus** · 01-25-2021, 07:06 AM

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

**hassan7** · 01-25-2021, 01:11 PM

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

**hassan7** · 01-25-2021, 01:12 PM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

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

Re: How can i delete or clear cells that exists bfore zero cell ?

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

Re: How can i delete or clear cells that exists bfore zero cell ?

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Delete row if value exists in cell

Can cell.Clear be made to delete down to next value

VBA to Clear 1 Cell and Clear other cells Formula only

[SOLVED] Clear cells to the left of active cell when it is delete/cleared

[SOLVED] if sheet 9 exists, clear it otherwise create one

Code to clear contents of specific cells based upon if key exists in other sheets

[SOLVED] Delete/clear a cell based on another cells contents

Bookmarks

Bookmarks

Posting Permissions