+ Reply to Thread
Results 1 to 3 of 3

How to insert "n" rows base on cell value and copy down data

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    810

    How to insert "n" rows base on cell value and copy down data

    Hello All,
    As Title said I'm looking for the code to insert "n" rows base on cell value at column A:A then copy down the data. I found code that can insert "n" rows but still need how to copy data down to the emty rows:
        Dim r, n, count As Range
        Dim LastRow As Long
        Dim temp As Integer
        Set r = Range("A:K")
        Set count = Range("A3:A" & Range("A" & Rows.count).End(xlUp).Row)
        LastRow = Range("A" & Rows.count).End(xlUp).Row
        For n = LastRow To 1 Step -1
            temp = Range("A" & n)
            If (temp > 0.9) Then
                Rows(n + 1 & ":" & n + temp).Insert Shift:=xlDown
            End If
        Next n
    Please see sample in attached.
    Regards,
    tt3
    Attached Files Attached Files
    Last edited by tuongtu3; 10-17-2014 at 12:24 AM. Reason: Solved

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to insert "n" rows base on cell value and copy down data

    Like so:

    Option Explicit
    
    Sub CopyRows()
    Dim LR As Long, Rw As Long
    
    With ActiveSheet
        LR = .Range("A" & .Rows.count).End(xlUp).Row
        For Rw = LR To 2 Step -1
            If .Range("A" & Rw).Value > 0 Then
                .Rows(Rw).Copy
                .Rows(Rw + 1 & ":" & Rw + .Range("A" & Rw).Value).Insert Shift:=xlDown
            End If
        Next Rw
    End With
    
    End Sub
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    810

    Re: How to insert "n" rows base on cell value and copy down data

    Hi JBeaucaire,
    The code works perfect and thank you very much for your help.

    Regards,
    tt3

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy Rows with "Closed" in Cell to the bottom of the existing data in Sheet5
    By Baxterma in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-07-2014, 10:30 AM
  2. Copy rows between "STARTCOPY" & "ENDCOPY" and insert in another worksheet
    By mikeop99 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-18-2013, 10:19 PM
  3. Copy the worksheet data "Base" for 31 tabs.
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-22-2012, 08:29 AM
  4. Base on column "J" insert new rows & copy formula parts to it
    By johncena in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-08-2011, 07:24 AM
  5. Problem Combining "Paste Link" & "Insert Rows"
    By o'h3nry in forum Excel General
    Replies: 1
    Last Post: 05-14-2010, 03:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1