Using Macro2 to iteratively call a Worksheet_Change Sub

buffalo

Board Regular
Joined
Jun 19, 2003
Messages
183
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:

Code:
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

jimboy

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

Code:
Range("I" & Range("i65536").End(xlUp).Row) = Range("I" & Range("i65536").End(xlUp).Row).Value
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,817
Messages
5,772,456
Members
425,760
Latest member
paphon

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