Run VBA on Event Change Delete Row

[atblack22]

I have some VBA built that handles code on worksheet Selection Change that works fine. But I have some formulas that it is automatically inserting referencing other cells.

However, when I have to delete a row (due to duplicate information), the cell formulas do not automatically update. I need the formula to either hold the cell reference dynamically, or and event Delete row procedure where I can refresh the formulas.

A formula I'm using is

ActiveCell.Offset(0, 2).Formula = "=IF(ISBLANK(B" & refrow & ")," & _ ActiveCell.Offset(-1, 2).Value & " +1,1)"

So its basically just adding a one to the cell value above it, but when I delete a row, it holds the same value because the cell reference is gone, and leaves the previous row's value in the formula, and no the cell reference....either way to handle this would work...

 

[AlphaFrog]

I'm not sure if I follow, but try something like this...

ActiveCell.Offset(0, 2).FormulaR1C1 = "=IF(ISBLANK(R" & refrow & "C2),R[-1]C + 1,1)"

 

[AlphaFrog]

Or this...

ActiveCell.Offset(0, 2).Formula = "=IF(ISBLANK(B" & refrow & ")," & _
ActiveCell.Offset(-1, 2)[COLOR="Red"].Address(0, 0)[/COLOR] & " +1,1)"

 

[atblack22]

Thats more of what I'm looking for, but the only thing that happens is when I delete the row, then I get the #REF error, which is what I'm trying to avoid. Thats why I was thinking I needed an Event Change Delete row recalculation or something....

 

[atblack22]

Just when i delete a row, then the rows below give me the #REF error, and I need a way that can automatically handle that. I have a recalculation button they can click and redoes all the formulas, but I would rather have that incorporated in to either not get the #REF when I delete, or if it does, the action of me deleting a row will automatically redo the formula to not have reference error.

 

[AlphaFrog]

Try this then...

colRef = Split(ActiveCell.Offset(0, 2).Address, "$")(1)
ActiveCell.Offset(0, 2).Formula = "=IF(ISBLANK(B" & refrow & "),INDIRECT(""" & colRef & """ & ROW()-1)+1,1)"

 

[atblack22]

PERFECT......Thanks so much.