Replacing/deleting values if doesn't satisfy condition

[excel newb]

Hi,

I have a column with "1" and "0". I'm looking for something like, if the value in a cell within the column is "0", then delete the 4 cells previous to the cell that contained "0" within the row. Instead of deleting, the values can also be replaced with anything e.g., zero or n/a etc. Whichever way it would work, would be great.

Thank you!

 

[VoG]

Hello and welcome to MrExcel.

Does this work as expected? Press ALT + F11 to open the Visual Basic Editor, Insert > Module and paste in:

Sub Deal0()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 5 To LR
    If Range("A" & i).Value = 0 Then
        Range("A" & i - 4).Resize(4).Value = [#N/A]
    End If
Next i
End Sub

Press ALT + F11 to return to your sheet, Tools > Macro > Macros, highlight Deal0 and click the Run button.

 

[excel newb]

Thank you.

I'm using excel 2007, so I'm not sure how to apply the last part to 2007:
Press ALT + F11 to return to your sheet, Tools > Macro > Macros, highlight Deal0 and click the Run button.



Also, when you specified "A", how would I specify A and more columns. Do I put & or , or something else?

Thank you

 

[VoG]

Click the Office button (top left), select Excel Options, tick Show Developer tab in the ribbon the click OK. Click Macros, highlight Deal 0 then click the Run button.

What is the range to which this macro should apply?

 

[excel newb]

Hi,

It's for columns "AO", "AU", "BA" and "BG".

Thank you.

 

[excel newb]

for 80 different excel files