#1
|
||||
|
||||
VBA to Insert variable rows based on a data in active cell
I have a table of current and delinquent taxpayers. What's the macro to automate the insertion of 10 entire rows above the cell where I typed 1979 (Column L). From cell 1979, the 10 inserted rows should contain 1986, above 1986 is 1993, above 1993 is 2005, above 2005 is 2011, then 2012, 2013, 2014, 2015, 2016, 2017. While Column K have values of 1980 to 2017.
It should look this way after running the macro. Col K Col L 2017 2017 2016 2016 2015 2015 2014 2014 2013 2013 2012 2012 2006 2011 1994 2005 1987 1993 1980 1986 1979 There should be a loop that when I type in the same column L but different row 1986, 9 rows are inserted above and the blank cells above 1986 contain 1993 to 2017, column K shows 1987 to 2017. If I type 1993, 8 rows are likewise inserted and 2005 to 2017 automatically appear on the blank cells and so on until 2015. Last edited by Marcia; 07-08-2018 at 02:40 PM. Reason: additional information |
#2
|
||||
|
||||
I need help; I'm helping the taxman.
Right click on the tab of the sheet you want this to work on, and choose View code, then copy and paste this code where the cursor is flashing: Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count = 1 Then If Not Intersect(Target, Range("L:L")) Is Nothing Then x = [{2017,2017;2016,2016;2015,2015;2014,2014;2013,2013;2012,2012;2006,2011;1994,2005;1987,1993;1980,1986;"",1979}] Z = Application.Match(Target.Value, Application.Index(x, 0, 2), 0) If Not IsError(Z) Then Z = Z - 1 If Z >= 8 Then '<<<adjust the 8 to the minimum number of rows to be inserted Application.EnableEvents = False Target.Resize(Z).EntireRow.Insert Target.Offset(-Z, -1).Resize(Z, 2).Value = x Application.EnableEvents = True Target.Offset(1).Select End If End If End If End If End Sub If Z >= 8 Then '<<<adjust the 8 to the minimum number of rows to be inserted |
#3
|
||||
|
||||
Delete one:
|
#4
|
||||
|
||||
VBA to Insert variable rows based on a data i active cell
Thank you p45Cal. It worked great, I changed the number 8 to 1 so the desired number of rows were inserted correctly. Again, big thanks.
|
#5
|
||||
|
||||
Me likes post #3
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
creating multiple data rows, for labels based on a cell value | josbor01 | Excel Programming | 3 | 01-02-2018 02:04 PM |
Would like a macro to copy a cell down a variable number of rows | Rod_Bowyer | Excel Programming | 6 | 03-25-2016 08:18 PM |
Perform calc in active cell then highlight based on formula result | grexcelman | Excel Programming | 4 | 01-12-2015 11:00 AM |
Insert values from multiple rows based on value in one column | pachmarhi | Excel | 3 | 07-18-2014 09:57 PM |
Hide Rows and Update Chart based on cell value | ubns | Excel Programming | 5 | 05-07-2012 05:44 AM |