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]

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?

Not 100%, i've never wrote a macro in my life

I've tried using the F8 button, it doesn't really tell me much

 

[wigi]

You should organize the screens so that you see column A of your spreadsheet, and next to it the VBA screen with my macro.

Then, put cursor in the macro and hit F8. A line will become yellow. Hit F8 again, and again until you finished the macro.

Use the mouse to hover over the variables, to know their values during the execution of the macro.

This is invaluable information to understand what you're doing.

 

[Busy N]

Below is the line which is throwing it out, it is deleting the '0' but not the 'block' of 11

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

 

[wigi]

Where do you see the TRUE value in the cells?

Only in that "5th" cell or in all cells to be deleted/shifted upwards?

 

[Busy N]

Where do you see the TRUE value in the cells?

Only in that "5th" cell or in all cells to be deleted/shifted upwards?

Thanks for that, i've got it working however, i need to do a macro similar to rules to the above but it need it not to search in 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.

I've tried changing the macro around but with know joy so far.

Thanks.

 

[wigi]

Thanks for that, i've got it working however, i need to do a macro similar to rules to the above but it need it not to search in 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.

I've tried changing the macro around but with know joy so far.

Thanks.

For instance:

Sub blocksofcells()

    Dim lRow As Long

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

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

I only changed the constants from my code above, no code itself. Use F8 to step through the macro and see what happens.

 

[Busy N]

It's not finding any information in the special cells section, below is what i'm testing this on, the 1st 11 rows should stay, these rows will never need to be deleted, so in this instance i would need row 12 to row 36 to be deleted.


:20:CCT TRF P04
:23B:CRED
:32A:110809GBP4,00
:50K:/information
Information
:57C://SCInfo
:59:/Info
Information
:70:TRF P04
:71A:SHA
:72:/REC/LCC-GB
-
:20:CCT TRF P05
:23B:CRED
:32A:110809GBP5,00
:50K:/00
0
:57C://SC0
:59:/0
0
:70:0
:71A:SHA
:72:/REC/LCC-GB
-
:20:CCT TRF P06
:23B:CRED
:32A:110809GBP6,00
:50K:/00
0
:57C://SC0
:59:/0
0
:70:0
:71A:SHA
:72:/REC/LCC-GB
-

 

[Busy N]

For instance:

Sub blocksofcells()

    Dim lRow As Long

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

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

I only changed the constants from my code above, no code itself. Use F8 to step through the macro and see what happens.

Thanks for your help it's working a treat now!!

 

[Busy N]

Hi,

Is there any way of doing with the value to find is #N/A rather than zero (0)?