Insert Specific Number of Rows with Data

Microsoft Excel 2003 (full product)
March 26, 2010 at 15:05:35
Specs: Windows XP
I am trying to be a template to save time.

I generally have 10 groups of data all will a few columns of repeating IDs. Within each group there is a different number of rows each month yet with the same data. For instance this month may have 16 rows of data within each of the 10 groups.

I would like to create a load type sheet so that I can enter the number of rows, and the data to be repeated in the first column of each row and then have excel automatically fill that number of rows for each group.

If I enter 9 for Number of Rows, and AAA for the ID column I would like to be able to hit a button that will create my (always) 10 groups with (in this case) one after another vertically with 9 rows in each group starting with AAA .

In addition I would like to be able to have each row in each group reflect a 001 for first group, 002 for next group, 003 and so on until 010...

That may make no sense at all but any starting point would be great!

My main issue is being able to enter the number of rows each of the 10 groups will have and having excel create them...

Thank you in advance!


See More: Insert Specific Number of Rows with Data

Report •

#1
March 26, 2010 at 17:50:24
I'm not clear what you mean by a "group" or where you want you AAA or 001, 002, etc. but you asked for a starting point so here it is.

Since I'm pretty sure it isn't what you want, tell me what is does right and what it does wrong and I'll try to fix it.

Open a new workbook, hit Alt-F11 to open the VBA Editor and paste this code in. Press F5 and follow the prompts.

Option Explicit
Sub CreateGroups()
Dim groupId
Dim numRows, nxtRow, nxtNum, rw As Integer
'Get Group ID string, Exit if Canceled
 groupId = Application.InputBox("Enter the Group ID", Type:=2)
  If groupId = False Then Exit Sub
'Get number of Rows for this Group, Exit if Canceled
  numRows = Application.InputBox _
   ("Enter the Number of Rows for Group ID " & groupId, Type:=1)
   If numRows = False Then Exit Sub
'Find next empty Row in Column A
  nxtRow = Range("A" & Rows.Count).End(xlUp).Row + 1
'Loop throgh rows, putting Group ID in Column A
'and 001, 002, etc in column B
   For rw = nxtRow To nxtRow + numRows - 1
    Range("A" & rw) = groupId
'Increment 001, 002, etc.
     nxtNum = nxtNum + 1
      If nxtNum < 10 Then
       Range("B" & rw) = "'00" & nxtNum
      Else: Range("B" & rw) = "'0" & nxtNum
      End If
   Next
End Sub


Report •

#2
March 27, 2010 at 07:23:23
Thank you for the quick response. Let me try to clarify what I mean...

Each month I have a sheet with 16 (not 10) groups -- this will never change there will always be 16.

However in each group the number of rows (records) will change from month to month. But each of the 16 groups will always have the same number of these rows depending on that months count.

Within these records there will be certain data that never changes, for instance a Criteria Column, a Group ID Code - the only thing that will change is how many times this data is replicated (the number of rows / records in each group) which I would like to tell excel how many there are.

So...for instance:

If my 4th row has all headings from left to right.
Going down there will be 16 groups (shaded light yellow first group, directly after no shading for second group, directly after shaded light yellow 3rd group...all the way to group 16.)
I want excel to ask me how many records are in each group, I enter the value and it creates that.

What I have now is row 4 headings.
Row 5 the first record in group 001
Row 6 the first record in group 002
etc to 016

Once I enter the number of records per group I would like excel to insert the additional number of rows -1 (for the first which is already present) after each row that is there.
While doing so I would like to have excel copy down (inside each group 001,002,003 etc. certain columns of which I have data which never changes.


So If I have
CRITERIA | GROUP ID
65+ | 001 (Shaded Light Yellow)
50-64 | 002 (No Shading)
40-49 | 003 (Shaded Light Yellow)
down to 016

I would want to enter the number of rows per Group and have it spit back:

If the current month has 4 rows recorder per group

CRITEERIA | GROUP ID
65+ | 001 (Shaded Light Yellow)
65+ | 001 (Shaded Light Yellow)
65+ | 001 (Shaded Light Yellow)
65+ | 001 (Shaded Light Yellow)
50-64 | 002 (No Shading)
50-64 | 002 (No Shading)
50-64 | 002 (No Shading)
50-64 | 002 (No Shading)
40-49 | 003 (Shaded Light Yellow)
40-49 | 003 (Shaded Light Yellow)
40-49 | 003 (Shaded Light Yellow)
40-49 | 003 (Shaded Light Yellow)
etc all the way til 016.

Obviously there are more columns which are constant and some that are not, but I feel seeing how to do this I could finish the rest of the columns that are constant on my own once I identify them.


I basically will have the first row of each group and will want to automatically fit in between each the rest of the rows (records) based on the number of rows I tell it they will have that month. And also copy down certain columns per group that I know will not be changing.

In addition if I have a specific formula in one of the rows I have as the basic starting point, will that be able to copy down too?


Report •

#3
March 27, 2010 at 14:16:25
Try the following code in a back-up copy of your workbook and let me know if it fits your needs.

As written, it copies the entire row based on the numbers of rows you enter in the InputBox.

re: I have a specific formula in one of the rows

You might need to explain this requirement in a little more detail. Since my code basically duplicates entire rows for each group, it will copy everything, including any formulae in the cells.

Option Explicit
Sub CreateGroups()
Dim numRows, firstRow, rw As Integer
 Application.ScreenUpdating = False
'Get number of Rows, Exit if Canceled
  numRows = Application.InputBox _
   ("Enter the Number of Rows Required", Type:=1)
    If numRows = False Then Exit Sub
'Initialize First Row counter
  firstRow = 5
'Loop through Groups, Inserting Rows
nxtGroup:
  For rw = 1 To numRows - 1
    Rows(firstRow).Copy
    Rows(firstRow + rw).Insert Shift:=xlDown
  Next
'Check to see if we have inserted row into all groups
   If firstRow = 15 * numRows + 5 Then Exit Sub
'If not, Increment firstRow counter to the
'beginning of next Group
     firstRow = firstRow + numRows
'Go to NxtGroup label and insert Rows
     GoTo nxtGroup
 Application.ScreenUpdating = True
End Sub


Report •

Related Solutions

#4
March 27, 2010 at 15:52:10
Thank you so very much. This seems to meet all of the needs I have right now! Once I get back on my regular PC I will check it out in more detail and see if there is anything else I may need tinkering with. But overall, this is great!

Is there any easy way to assign this to a button that I can place directly within the sheet?


Report •

#5
March 27, 2010 at 17:25:02
A couple of options...

1 - Insert any shape from the Drawing toolbar
2 - Righ-click the shape and choose Assign Macro
3 - Select the Macro name from the list and click OK

or

1 - From the Tools toolbar choose Customize
2 - Click the Commands tab
3 - Scroll down to Macros
4 - Drag the Smiley face up to any of the tools bars on your worksheet.
5 - With the Customize dialog box still open, Right-click the button and choose Assign Macro
6 - Select your macro and click OK
7 - With the Customize dialog box still open, Right-click the button and choose Edit Button Image or Change Button Image. Edit/change the image so that you'll know what macro it is assigned to. Trust me, once you have more than a couple of macros assigned to tool bar buttons, you'll need something to remind you which button does what.
8 - Close the Customize dialog box and save the workbook

Once you Customize any menu or toolbar, Excel will automatically create an excel.xlb file. Excel will then use this .xlb file to build your customized menus every time you open Excel.

One more tip:

Create a personal.xls file, hide it and save it in your xlstart folder. Whenever you open Excel, this file will open (hidden). If you save your macros in the personal.xls file then they will be available in every workbook that you open.


Report •

#6
March 29, 2010 at 07:02:32
THANK YOU! Awesome!


Once the groups have been created there is a unique % that I want to display for each row, is there a way to have a separate work sheet with a simple column of these % that will automatically fill into the main sheet in the corresponding position (they will be entered as they change in the other spreadsheet just as they would appear in the main group --- it would just need to again repeat for each group, for instance, if it were listed vertically
8%
10%
9%
Down to the last depending on the number of rows we needed

It would replicate that column in each group that our last module created in the correct space for instance if it were going to be in column F


Going from Column A to Q,

A- would be constant – will work fine with first part of the module you provided.
B- Same As F above yet with a unique word
C - Blank - will work find with the first part of the module you provided
D is the Group ID – will work find with the first part of the module you provide
E - Constant - will work find with the first part of the module you provided
F- Mentioned above for %
G- Formula - will work find with the first part of the module you provided
H- Formula - will work find with the first part of the module you provided
I – Unique Word Description, repeats as mentioned above would like to use same sheet and list a column as it would be for one group have it repeat
J- Dates would like to be same as F and I
K – Would like to number 1,2,3,4 going vertically to reflect number of rows I needed for each group and repeat 1-# rows specified for each group again
L – have to come back to this one
M – Constant - will work find with the first part of the module you provided
N – Constant - will work find with the first part of the module you provided
O- Formula - will work find with the first part of the module you provided
P- Constant - will work find with the first part of the module you provided (OR see below)
Q- Constant -will work find with the first part of the module you provided (OR see below)

P,Q = will work find with the first part of the module you provided however if possible would like a one cell entry for each on the “Load Worksheet” just to enter it once then have it repeat for the entire 16 group set.

I believe this would be similar for Columns B, F, I, and J - exept instead of asingle cell entry it would be a single group in a column that would need to be repeated for each of the 16 groups.

And K could be like this although I would never change the values, just have them count from 1 to number of rows in the group then repeat for each group.

Primarily I am looking to have those columns listed horizontally on the Load Worksheet and be able to change their values each month – then have whatever I enter there automatically populate in the appropriate columns repeating, for each group in the main worksheet!


So overall I would like to fill in the certain columns mentioned by having a separate worksheet which I dubbed the Load Work Sheet, that will have groups 1-16 and have a single row from each- of which I would like to type in the correct order as they would appear in each group on the main worksheet, and those single instances would repeat within each group depending on the number of rows I specify.

If this is too much, please let me know, I don’t want to take advantage of anyone’s help, but your help is actually TREMENDOUSLY helpful. I have been able to learn and love to pick things apart to use in my own way in the future.

Sorry if this is lengthily just tried to explain as clearly as possible – but again I solute you your are such a huge HUGE help!


Report •

#7
March 29, 2010 at 11:49:53
I added a section to fill in the columns you mentioned with data from Sheet2.

Since I can't see your worksheets from here, I assumed the data you need is in Sheet2!A1:D16.

Hopefully you can understand what I need and adjust the code to match your layout.

Option Explicit
Sub CreateGroups()
Dim numRows, firstRow, rw As Integer
 Application.ScreenUpdating = False
'Get number of Rows, Exit if Canceled
  numRows = Application.InputBox _
   ("Enter the Number of Rows Required", Type:=1)
    If numRows = False Then Exit Sub
'Initialize First Row counter
  firstRow = 5
'Loop through Groups, Inserting Rows
nxtGroup:
  For rw = 1 To numRows - 1
    Rows(firstRow).Copy
    Rows(firstRow + rw).Insert Shift:=xlDown
'Get data from Sheet2, filling in existing Row first
    Cells(firstRow + rw - 1, "B") = Sheets(2).Cells(rw, "A")
    Cells(firstRow + rw - 1, "F") = Sheets(2).Cells(rw, "B")
    Cells(firstRow + rw - 1, "I") = Sheets(2).Cells(rw, "C")
    Cells(firstRow + rw - 1, "J") = Sheets(2).Cells(rw, "D")
    Cells(firstRow + rw - 1, "K") = rw
  Next
'Get data for last inserted row
    Cells(firstRow + rw - 1, "B") = Sheets(2).Cells(rw, "A")
    Cells(firstRow + rw - 1, "F") = Sheets(2).Cells(rw, "B")
    Cells(firstRow + rw - 1, "I") = Sheets(2).Cells(rw, "C")
    Cells(firstRow + rw - 1, "J") = Sheets(2).Cells(rw, "D")
    Cells(firstRow + rw - 1, "K") = rw
'Check to see if we have inserted row into all groups
   If firstRow = 15 * numRows + 5 Then Exit Sub
'If not, Increment firstRow counter to the
'beginning of next Group
     firstRow = firstRow + numRows
'Go to NxtGroup label and insert Rows
     GoTo nxtGroup
 Application.ScreenUpdating = True
End Sub


Report •

#8
March 30, 2010 at 16:02:52
Didnt really seem to work, is there a way I can send you the basic layout?? Thanks!

Report •

#9
March 30, 2010 at 17:15:23
I've sent you an email addres via PM. Please do not share it with anyone.

Report •

#10
March 30, 2010 at 17:47:54
Thank you I sent you an email, with my User Name as the subject -- I will not share the address.

Report •

Ask Question