Run recorded macro only in recent modified rows

[Anbuselvam K]

Hi

In the attached excel sheet, I have recorded the macro to run the calculation steps as below.

Macro Key is Ctrl + g

Step 1 - Copy formulas from S2 to DK2
Step 2 - Paste it to S8 to DK32
Step 3 - click F9 (Manual Calculation)
Step 4 - Copy S8 to DK32
Step 5 - Paste values in S8 to DK32
Step 6 - F9

By running the above macro, sheet got calculated S8 to DK32, In my actual file I have S8 to DK9600 to calculate where excel quit due to the heavy calculation.

My desired result is,

The above function should run only the row number 27 (S27 to DK27) if I entered a value from C27 to I27

Expecting positive reply. Thanks in Advance.

 

[Hui]

try this:

Sub Run_Calc()
'
' Run_Calc Macro
'
' Keyboard Shortcut: Ctrl+g
'
    Dim lr As Integer
    lr = Range("A" & Rows.Count).End(xlUp).Row
    If ActiveCell.Row <> lr Then Exit Sub
    lr = lr + 1
    Range("S2:DK2").Copy
    Range("S" & lr & ":DK" & lr).PasteSpecial Paste:=xlPasteValues, _
      Operation:=xlNone, _
      SkipBlanks:=False, _
      Transpose:=False
    Application.CutCopyMode = False
    Calculate
    Range("C" & lr).Select
End Sub

 

[Anbuselvam K]

Dear Mr Hui

I have added as you suggest. after running, it is pop up GO To menu and asking the cell reference. Please find the attached copy.

Here I would like to request you to replace the existing code with your new code and send with attachment. So that I can easily understand what was the changes and how it was effected in the output.

Thanks in Advance.

 

[Hui]

Delete the macro Run Calc from your file
Record a new macro and give it a name Run_Calc and assign it a Ctrl+G Shortcut
Just change cells
then Stop Recording the new macro
Goto VBA
Find the new Run Calc macro

Remove the line where it changes cells
Copy the code below into the new macro, between the Sub and End Sub lines

Enjoy

'
' Run_Calc Macro
'
' Keyboard Shortcut: Ctrl+g
'
  Dim lr As Integer
    lr = Range("A" & Rows.Count).End(xlUp).Row
    If ActiveCell.Row <> lr Then Exit Sub
    lr = lr + 1
    Range("S2:DK2").Copy
    Range("S" & lr & ":DK" & lr).PasteSpecial Paste:=xlPasteValues, _
      Operation:=xlNone, _
      SkipBlanks:=False, _
      Transpose:=False
    Application.CutCopyMode = False
    Calculate
    Range("C" & lr).Select

 

[Anbuselvam K]

Dear Mr Hui

This changes made nothing happen after running the macro. check the attachment and run yourself and see the result.

Please modify and send as an attachment. After getting successful running, I need to implement this to nearly 10000 rows excel sheet. I hope there is no much change.

Thanks in advance

 

[Hui]

Firstly, Delete Rows 28:32

in cell I27
Ctrl+G



Works as you wanted ?

 

[Anbuselvam K]

Dear Mr Hui

Thanks a lot for your suggestion and your effort. Here I explained my observation and required details for your study.

My apologies if my explanation very hard to understand.

Firstly, As you said, After deleting row 28 to 32 and select I27 the macro is running (It means it will work only in the last row and cannot be anywhere before like I10, I20 and etc... please make sure it should work I8 to I32 and I10000 if we added)

Second, The result is showing #value in some cells after runs the macro, But it is showing the desired value if you manually copy S2 to DK2 and paste in any rows between S8: DK8 to S32: DK32, So there is a problem with copy, paste and calculate in the macro code.

Please note,

The step is not copying and paste value.
The step is copy > Paste formula > calculate (F9) > then copy > Paste value > Calculate (F9).

I hope you understand the above and expecting your positive reply.

 

[Hui]

same instructions as before

Paste the code below into the existing module

' Run_Calc Macro
'
' Keyboard Shortcut: Ctrl+g
'
Dim lr As Integer
    lr = Range("A" & Rows.Count).End(xlUp).Row
    If ActiveCell.Row <> lr Then Exit Sub
    lr = lr + 1
    Range("S2:DK2").Copy
    Range("S" & lr & ":DK" & lr).Paste
   
    Calculate
   
    Range("S" & lr & ":DK" & lr).Copy
    Range("S" & lr & ":DK" & lr).PasteSpecial Paste:=xlPasteValues, _
      Operation:=xlNone, _
      SkipBlanks:=False, _
      Transpose:=False
    Application.CutCopyMode = False
   
    Range("C" & lr).Select

 

[Anbuselvam K]

Dear Mr Hui

Sorry for the delay in reply.

Please check the attached screenshot and the error message for your information and further slight modification.

 

[Anbuselvam K]

Dear Mr Hui

The below code is for your information and it is working perfectly.


Sub Run_Calc()
'
' Run_Calc Macro
'
' Keyboard Shortcut: Ctrl+w
'
Set myrng = Intersect(Selection.Areas(1).EntireRow, Range("S:DK"))
Range("S2:DK2").Copy myrng
Calculate
myrng.Value = myrng.Value
Calculate
Range(Range("C7"), Range("C7").End(xlDown)).Select
End Sub