Using Macro2 to iteratively call a Worksheet_Change Sub


Board Regular
Jun 19, 2003

I have data in COl A thru I and caluculations in Col L thru R.

I enter new data in a new row under these columns A:I,

As soon as I enter new data and reach Col I (which is the last information entry), the Target.Column = 9 and triggers the code inside a Worksheet_change sub to alter ALL rows in Col L thru R

For e.g.

New entry in row 6 under Col A thru I will cause L1:R6 to change.
the Worksheet_change causes these changes, the code is in principle as below:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Row = 1 Then Exit Sub
    If Target.Column = 9 Then
    Dim Rng As Range
    Dim c As Range 

    Set Rng = Range("L2:L" & Range("L65536").End(xlUp).Row)
    For Each c In Rng

    	{Do something }
'This modifies all columns L:R and ALL rows.
' I think changng this range should do the job, but HOW?

    Next c

end Sub()

Now if one is to delete one of the rows, say row 4, the calculations columns will be a big mess (as they will include some part of the deleted entries)

I want to have a macro Macro2 to iteratively call Worksheet_change so that
the sheet "relives" and recalculates the WHOLE process again as if the deleted entry never existed.

So when the user deletes some column xyz, the data shifts up and the calculations are a mess.

I use the Macro2 (maybe using Ctrl + m) to recalculate everything after any row deletion is made.
the worksheet_change function is 'somehow' called and only row 1 is considered and calculation in L1:R1 is made.

Next the Macro2 calls worksheet_change again ... and entry in row 2 Col A thru I is considered as a new entry. Now L1:R2 will be recalculated.

goes on till the last entry row in A:I where L1:Rlastrow

...effectively a 'full run' calculating and recalculating:

for every row x in A:I ...
Recalculate L1:Rx

Maybe setting the ranges appropriately in the worksheet_change function and writing a Macro2 which calls the worksheet_change will do the job?

any suggestions are welcome.


Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.


Well-known Member
Apr 11, 2002
What about adding this line to the bottom of Macro2 to force the worksheet_change event?

Range("I" & Range("i65536").End(xlUp).Row) = Range("I" & Range("i65536").End(xlUp).Row).Value

Watch MrExcel Video

Forum statistics

Latest member