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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,674
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,674
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,914
Messages
5,483,689
Members
407,399
Latest member
Rakeforms

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top