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!!
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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?
 

Busy N

New Member
Joined
Nov 14, 2011
Messages
45
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.
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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?
 

Busy N

New Member
Joined
Nov 14, 2011
Messages
45

ADVERTISEMENT

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.
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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?
 

Busy N

New Member
Joined
Nov 14, 2011
Messages
45

ADVERTISEMENT

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.
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
So, there could be a zero in each of the 11 cells, or only in the 5th cell?
 

Busy N

New Member
Joined
Nov 14, 2011
Messages
45
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.
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,054
Members
414,357
Latest member
Gemma_R

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
Top