How to automatically delete formulas in an adjacent column

[Roger on Excel]

I have two columns (A and B) which return results from formulas in other cells.

I would like to have a macro read down the list of column A and if the
result returned is empty/zero, the contents formula of its adjacent column B
cell be deleted.

I have complex code which doesnt work when the column B result returns a
blank (which is the case when A is also blank), so this solution, although
drastic will solve my problem adequately.

Can anyone help?

Thanks,
Roger

 

[Sandy Mann]

Assuming that you do not have any data below the data in Column B that you
want to work on try somehting like:

Sub DeleteIt()
Dim LastRow As Long
Dim Counter As Long

LastRow = Cells(Rows.Count, 2).End(xlUp).Row

Application.ScreenUpdating = False
For Counter = LastRow To 1 Step -1
If Cells(Counter, 1).Value = "" Then _
Range(Cells(Counter, 1), Cells(Counter, 2)).Delete shift:=xlUp
Next Counter
Application.ScreenUpdating = True
End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[JLGWhiz]

If column B contains formulas and you delete one that equals zero or is
blank, then shifting the cells up will make cells in column B at the bottom
of the list become blank and present the same problem for the corresponding
data in column A.
You might be better off by just using a conditional If statement to avoid
the error message you are receiving. i.e. If column B value <> 0 Then do
something. That way if the value in column B is 0 it will bypass that cell.
It is difficult to determine the best method without seeing the problem code.