Automatically deleting cells above and below a value

[Busy N]

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

 

[Busy N]

That's correct the whole block needs to be deleted.

it will start from row 1.

 

[wigi]

Try the untested...

Sub blocksofcells()

    Dim lRow As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    For lRow = 5 To Range("A" & Rows.Count).End(xlUp).Row Step 11
        If Range("A" & lRow).Value = 0 Then Range("A" & lRow).Offset(-4).Resize(11).Value = True
    Next
    
    Columns(1).SpecialCells(2, 4).Delete xlShiftUp

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

 

[Busy N]

Hi,

Thanks for that, however it didn't seem to work, its coming up with a message that no cells are being found for the below line:-

Columns(1).SpecialCells(2, 4).Delete xlShiftUp

Thanks

 

[wigi]

Then, this would mean that no 5th cell in every "block" of 11 cells is equal to 0...

For that, the code checks A5, A16, A27, A38 and so on.

Can you check this in the sheet?

 

[Busy N]

Thanks,

It's now working but it's only deleting out the "0" and not the whole 'block' of cells - any idea's?

 

[wigi]

Are the rows with 0's shifted up after the macro runs? Or do you see an empty cell where the 0 used to be?

 

[Busy N]

The Cells are shifting up.

 

[wigi]

I cannot reproduce this.

Either all 11 cells disappear, either none of the 11 cells of a block disappears.

 

[Busy N]

Hi,

Could you write a script that is similar rules to the above but does not search for the 1st 11 rows (the macro should start on cell A12), this time in blocks of 12, then if the 6th row equals 0 then that row and the whole block of 12 needs to be deleted.

Thanks

 

[wigi]

Hi,

Could you write a script that is similar rules to the above but does not search for the 1st 11 rows (the macro should start on cell A12), this time in blocks of 12, then if the 6th row equals 0 then that row and the whole block of 12 needs to be deleted.

Thanks

Do you understand the code I posted earlier?
Did you step through the code by pressing the F8 button?
Were there any questions on that code left unanswered?