Remove the value if condition meet

Vishaal

Well-known Member
Joined
Mar 16, 2019
Messages
530
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Hi,

Thanks in advance,

Need help to remove the value in coloumn in following sheet if only "0" coming

Coloumn A
8
6
0
0
2
7
0
0
0

Formula or VBA will delete all the "0" after 7
I have also coloumn B, C, D and E
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
<style type="text/css"> table.tableizer-table { font-size: 12px; border: 1px solid #CCC ; font-family: Arial, Helvetica, sans-serif; } .tableizer-table td { padding: 4px; margin: 3px; border: 1px solid #CCC ; } .tableizer-table th { background-color: #104E8B ; color: #FFF ; font-weight: bold; }</style>
DataResult
82
63
06
07
2
7
0
0
0

<thead>
</thead><tbody>
</tbody>

=IFERROR(AGGREGATE(15,6,ROW($A$2:$A$10)/($A$2:$A$10<>0),ROWS($B$1:B1)),"")
 
Last edited:
Upvote 0
It's showing error on your formula in last point "", pls help
 
Upvote 0
works fine for me, where are you putting the formula ??
 
Upvote 0
In result i need

Data
8
6
0
0
2
7 All the zero removed

This result ok
 
Last edited:
Upvote 0
Ok, still works fine for me.....BUT as asked, which column are you putting the formula in?
 
Upvote 0
Maybe,

In B2, copied down :

=IF(ROW(A2)<=LOOKUP(1,0/$A$1:$A$100,ROW($A$1:$A$100)),A2,"")

Regards
Bosco
 
Upvote 0
Its showing

The formula you typed contains an error
 
Upvote 0
never mind...self deleted
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top