Line Insert

SH Harbour

New Member
Joined
Aug 22, 2023
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Good Afternoon

I am struggling to find a Macro that will permit me to insert a row dependent on a number in another cell, for example I have the following:

1738767174195.png


There is a drop down box with numbers 0 - 9, is it possible to create a Macro that will insert beneath the number of lines corresponding the quantity in the cell above (I18), so if I18 changed to 4 it would insert another row and push down everything beneath, it would appear as follows etc:

1738767375750.png


Thank in advance for any assistance.
 

Attachments

  • 1738767146485.png
    1738767146485.png
    11.4 KB · Views: 3
  • 1738767350873.png
    1738767350873.png
    11 KB · Views: 3

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Do you have some end in mind? Sometimes VBA is not the best answer - in this case, since you're only going up to 9, what would be the advantage over selecting row 19 or row 23 and inserting a new row manually? How often do you expect to need to do this?
 
Upvote 0
Try. Worksheet event is used. Macro is for maximum rows of 9. If it is to be changed, change 9 by the required number in the macro( MaxRos)
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$18" Then
Application.EnableEvents = False

Dim Rng As Range
Dim Ro&, T&, MaxRos&
MaxRos = 9
Set Rng = Range("A19")
Ro = Target

If Ro <= MaxRos Then
Rng.UnMerge
Rng.Resize(Ro, 8).Merge
For T = 1 To Ro
If T <= Ro Then
Range("I18").Offset(T, 0) = T
Range("J19").Offset(T, 0).Resize(1, 3).Merge
End If
Next T
With Rng.Resize(Ro, 12)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.LineStyle = xlContinuous
.Font.Bold = True
End With
If Ro < MaxRos Then
With Rng.Offset(1, 0).Resize(MaxRos - Ro, 12)
.Clear
.UnMerge
End With
End If

End If
End If
Application.EnableEvents = True

End Sub
How to use worksheet event code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Select worksheet in drop down instead of General
Paste the code
Close the VB window.
Save the file as .xlsm
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,134
Members
453,642
Latest member
jefals

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