Excel/Help with Macros


Dear Steve,

I'm trying to develop a macro that will allow me to copy the data x number on times based on on the value of a certain cell. Id like for the macro to then continue on to the next value and do the same based on the the data on X. I have attached an image to further illustrate what I am talking about.

So far I have a Script that inserts blank lines. I was trying to modify it so it could do my bidding but I failed.

Here is the script:

Sub Macro1()
   Dim currRow As Long
   Dim currCell As String
   Dim checkCell As String
   Dim startRow As Long
   Dim endRow As Long
   Dim startCell As String
   Dim endCell As String
   Dim strRange As String
   Dim r As Range
   currRow = 2
   currCell = "a" & currRow
   checkCell = "a" & currRow + 1
   Do While Range(checkCell) > 0
       startRow = currRow + 1
       endRow = startRow + Range(currCell) - 1
       startCell = "a" & startRow
       endCell = "a" & endRow
       strRange = startCell & ":" & endCell
       Set r = Range(strRange)
       currRow = endRow + 1
       currCell = "a" & currRow
       checkCell = "a" & currRow + 1
End Sub

Thank you for taking the time to help me with this questions.



Hi Conrad,

This macro should work for you:

(Instead of asking how many to copy down, type the number that you want TOTAL).

Using your provided example, instead of typing 3 and having 3 rows created, you would type 4 and have 4 rows of that value total.

Please let me know if this gets you close to where you would like to be.

Sub AE_Conrad()
Dim lngStartRow As Long
Dim lngEndRow As Long
Dim m As Integer
Dim n As Long

With ActiveSheet
   lngStartRow = Range("G" & CStr(Application.Rows.Count)).End(xlUp).Row
   lngEndRow = 1

       For n = lngStartRow To lngEndRow Step -1
         If IsNumeric(Range("G" & CStr(n)).Value) Then

         For m = 2 To Range("G" & CStr(n)).Value
         Rows(n + 1).Insert
       Next m
         End If
       Next n
   End With
End Sub

Kind regards,
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts




I am proficient in all areas of MS Excel, including Visual Basic (Macros), Charting, Graphing, Custom User Forms, Template Layout and Creation, Developer Add-ins, Active-X Controls, Setting Advanced Properties, XML Data and Workbook Protection.


I have an extensive educational, vocational and business background extending over 12 years in information technology.

Association of Information Technology Professionals IEEE Computer Society Texas Professionals Texas Young Professionals Excel Developers Excel Blackbelts

Microsoft Certified Technology Specialist (MCTS) Program Microsoft Office Expert Program

©2016 About.com. All rights reserved.