From ActiveCell.Value to automatic

wilhelm

New Member
Joined
Mar 26, 2013
Messages
16
I have a list with over 50000 rows. Today, I use this macro manually to insert new lines based on an active cell value:
Code:
Sub Insert_Rows()
r = ActiveCell.Row
p = ActiveCell.Value
    NumLines = 13 - p
          If NumLines = 0 Then
        GoTo EndInsertLines
    End If
    Do
    r = ActiveCell.Row
    ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown
       Range("D" & r + 1 & ":AC" & r + 1).Formula = Range("D" & r & ":AC" & r).Formula
        Count = Count + 1
    Loop While Count < NumLines
EndInsertLines:
End Sub

I have created a dummy variable in column A to tell me where I need to run the macro. So I thought there must be a way to automatically insert new lines. Every time there is a cell with value "1" in column A I want to run the macro, but I'm facing troubles since the macro is written with ActiceCell.Value, and I need it to be done automatically.

0
0
0
0
1: run macro
0
0
0
0
0
0
0
1: run macro

Do you know how to help me?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hello Wilhelm,

Does this work for you?

Add another sub to search the rows for '1' in column A and then call your sub 'Insert_Rows' with that cell as input (replacing the ActiveCell part).

Code:
Sub searchRows()    
    Dim ac As Range
    lst = Range("A1").End(xlDown).Row
    
    For i = 1 To lst
        If Cells(i, 1) = 1 Then
            Set ac = Cells(i, 1)
            Call Insert_Rows(ac)
        End If
    Next i
End Sub


Sub Insert_Rows(ac As Range)
r = ac.Row
p = ac.Value
    NumLines = 13 - p
          If NumLines = 0 Then
        GoTo EndInsertLines
    End If
    Do
    r = ac.Row
    ac.Offset(1).EntireRow.Insert Shift:=xlDown
       Range("D" & r + 1 & ":AC" & r + 1).Formula = Range("D" & r & ":AC" & r).Formula
        Count = Count + 1
    Loop While Count < NumLines
EndInsertLines:
End Sub
 
Upvote 0
Thank you very much! I am almost there, when there is a cell in column A with value "1", I want the macro to look at the value in Column D at the same row(where there is a number between 1-13 - before I used it with ActiveCell.Value from this column) and run
Insert_Rows(ac As Range) based on the value in Column D.
So for example. The macro finds a value of 1 in Column A at row 4, at the same row in column D there is a value of 10. Then the macro should insert 3 new rows, as the old macro is written.
 
Upvote 0
In other words,I think
Code:
 [LEFT][COLOR=#333333]p = ac.Value
should be the value in column D and not A as it is now.[/COLOR][/LEFT]
 
Upvote 0
Thank you very much! It is exactly how I wanted it.
But the macro stops before it has been through the whole list.
I tested 100 rows, where there were six times that the macro should kick in. But it only works for the first four. Is it possible to force the macro to continue?
 
Upvote 0
I think the problem occurs because of this line of code
Code:
lst = Range("A1").End(xlDown).Row

Maybe it helps if you change the column into one where all cells from the first till the last cell is filled. Or just give lst a value of the number of rows.
You may also send me the workbook with (sample) data.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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
Back
Top