Automatically deleting cells above and below a value

Busy N

New Member
Joined
Nov 14, 2011
Messages
45
Hi,

I need to create a formula (probably within a macro) to delete all lines above and below if the 5th line equals zero.

An example is below,

-
:20:CCT TRF P05
:23B:CRED
:32A:110809GBP5,00
:50K:/00
0
:57C://SC0
:59:/0
22
:70:0
:71A:SHA
:72:/REC/LCC-GB

Any tips or advise on how to do this would be welcomed!!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
What is the area of cells you are looking at? Is it a selection made by the user? Is it a recurring pattern in a column of a sheet?
 
Upvote 0
The total area of cells is around 3600, all in the sequence of the 11 cells shown above.

In theory when a line is found to equal zero the above 4 lines needs to be deleted and every single cell below needs to be deleted as well.

The user will enter information in a separate tab, this information will then filter through to a new tab creating the above information, but anything that has a value of zero is not required to be included in the spreadsheet.
 
Upvote 0
Do you want to delete the entire row (shifting up other rows), or only clear the cells?

Do the cells have constants or formulas?

Is there an empty row between 2 blocks of cells?
 
Upvote 0
If a line is equal to zero then every line beneath is not needed as no more information will be input from the user, however unwanted formulas will still pull through from the 'holding' tab.

The whole row can be deleted however there is only ever going to be information in column A.
 
Upvote 0
IThe whole row can be deleted however there is only ever going to be information in column A.

Even if only column A contains data, deleting a row is not the same as clearing a cell...

If a line is equal to zero then every line beneath is not needed as no more information will be input from the user, however unwanted formulas will still pull through from the 'holding' tab.

Can you also reply to my other questions please?
 
Upvote 0
Apologies i should of been a bit clearer - All lines need to be deleted, rather than cleared.

There are no empty rows between cells.

There is a formula in every cell.
 
Upvote 0
So, there could be a zero in each of the 11 cells, or only in the 5th cell?
 
Upvote 0
If Cell 5 has a value of zero then its likely that line 8 will also have a value of zero the rest will always have some sort of text in.
 
Upvote 0
If the 5th cell is 0, then you want to delete the fill "block" of 11 cells?

On what row will the data start? Row 1 or row 2?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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