Run VBA on Event Change Delete Row

atblack22

New Member
Joined
Jun 16, 2011
Messages
10
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...
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,399
I'm not sure if I follow, but try something like this...

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

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,399
Or this...
Code:
ActiveCell.Offset(0, 2).Formula = "=IF(ISBLANK(B" & refrow & ")," & _
ActiveCell.Offset(-1, 2)[COLOR="Red"].Address(0, 0)[/COLOR] & " +1,1)"
 

atblack22

New Member
Joined
Jun 16, 2011
Messages
10
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

New Member
Joined
Jun 16, 2011
Messages
10

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,399
Try this then...
Code:
colRef = Split(ActiveCell.Offset(0, 2).Address, "$")(1)
ActiveCell.Offset(0, 2).Formula = "=IF(ISBLANK(B" & refrow & "),INDIRECT(""" & colRef & """ & ROW()-1)+1,1)"
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,143
Members
415,880
Latest member
Bruce0203

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top