Using Macro2 to iteratively call a Worksheet_Change Sub

[buffalo]

Hi,

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.

thanks

 

[jimboy]

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