Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-08-2018, 02:27 PM
Marcia's Avatar
Marcia Marcia is offline VBA to Insert variable rows based on a data in active cell Windows 7 32bit VBA to Insert variable rows based on a data in active cell Office 2007
Expert
VBA to Insert variable rows based on a data in active cell
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default 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
Reply With Quote
  #2  
Old 07-15-2018, 11:24 AM
p45cal's Avatar
p45cal p45cal is offline VBA to Insert variable rows based on a data in active cell Windows 10 VBA to Insert variable rows based on a data in active cell Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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
Go back to the sheet and enter 1979, 1986 or 1993 somewhere in column L.Currently, it works only for those three years, but this can easily be adjusted to other numbers by changing the line:
If Z >= 8 Then '<<<adjust the 8 to the minimum number of rows to be inserted
Reply With Quote
  #3  
Old 07-27-2018, 04:28 AM
p45cal's Avatar
p45cal p45cal is offline VBA to Insert variable rows based on a data in active cell Windows 10 VBA to Insert variable rows based on a data in active cell Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Delete one:
  • Thanks p45cal, that worked a treat.
  • That didn't work; what rubbish.
Reply With Quote
  #4  
Old 07-28-2018, 09:13 AM
Marcia's Avatar
Marcia Marcia is offline VBA to Insert variable rows based on a data in active cell Windows 7 32bit VBA to Insert variable rows based on a data in active cell Office 2007
Expert
VBA to Insert variable rows based on a data in active cell
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default 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.
Reply With Quote
  #5  
Old 07-28-2018, 10:02 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline VBA to Insert variable rows based on a data in active cell Windows 7 64bit VBA to Insert variable rows based on a data in active cell Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


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
VBA to Insert variable rows based on a data in active cell 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
VBA to Insert variable rows based on a data in active cell Insert values from multiple rows based on value in one column pachmarhi Excel 3 07-18-2014 09:57 PM
VBA to Insert variable rows based on a data in active cell Hide Rows and Update Chart based on cell value ubns Excel Programming 5 05-07-2012 05:44 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:57 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft