Insert and duplicate rows based on numerical value -1 in column

Amatu

New Member
Joined
Jul 21, 2019
Messages
2
Hello! I would greatly appreciate assistance in finding a solution in the excel ribbon or a VBA Code to automate the following task. I am creating a spreadsheet that is a download of all purchases that I have made for my music store since opening 6 years ago. Each row represents 1 item on a PO and the data related to that item (including Purchase Order #, Item #, Quantity ordered, cost per item, extended cost, etc). I am manually inserting a varied Number of blank rows based on the Quantity Value less 1 in row "M" and then copying the data from that row in the blank row(s). I need this macro to work on all 16,074 lines of data. Can you help me automate this function?
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,788
Office Version
2019
Platform
Windows
Code:
Option Explicit

Sub InsertRows()
    Dim i As Long, lr As Long
    Dim crit As Integer
    lr = Range("M" & Rows.Count).End(xlUp).Row
    For i = lr To 1 Step -1
        crit = Range("M" & i).Value - 1
        If Range("M" &  i) <> 1 Then
            Range("A" & i).EntireRow.Offset(1).Resize(crit).Insert Shift:=xlDown
            Range("A" & i).EntireRow.Copy Range("A" & i).EntireRow.Offset(1).Resize(crit)
        End If
    Next i


End Sub
 
Last edited:

Amatu

New Member
Joined
Jul 21, 2019
Messages
2
Please excuse my ignorance - my first time looking at VBA. I have set up a Command Button on my Spreadsheet that, I hope, when i click it, it will run the macro. Therefore, do I paste this CODE between the two commands on the code form?

Below is what i see before pasting the code:


Private Sub CommandButton1_Click()


End Sub
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,788
Office Version
2019
Platform
Windows
Yes, but delete the first line of my code beginning with Sub and delete the last line of code End Sub
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,087
Messages
5,509,172
Members
408,712
Latest member
Eve05

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top