+ Reply to Thread
Results 1 to 6 of 6

Need to Duplicate Data After Adding a Blank Row

  1. #1
    Registered User
    Join Date
    08-20-2012
    Location
    Portland, Ore
    MS-Off Ver
    Excel 2007
    Posts
    9

    Need to Duplicate Data After Adding a Blank Row

    I have a database formatted as the attached spreadsheet. What I would like to do is to run a macro (if possible) that takes each cell with a value and duplicates it below itself, if the following cell is blank. There are some instances where there are multiple blank cells, and I'd rather not have one cell duplicated in each 4 blank cells, which is what has been happening when I've been using a copy/paste method. I've been using "=B3" in B4 to refer to the cell above it, and just copying and pasting that in all blank cells, which I've filtered out. I have to do this in column B, and then move over and do this in columns F, G, etc., to duplicate all the data that was in the cells above. This works for the most part, but takes longer than I'd like it to when some workbooks have 3,000 rows of data. It also causes the problem of duplicating data in more than one cell, which should not take place. (Does that make sense?)

    I've run a macro already that will add the second row after each row already present. Is there a way to simply duplicate each row after this stage, thus resulting in a blank row after every row?

    Hope that make sense!

    Thank you,
    Ashley
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Need to Duplicate Data After Adding a Blank Row

    Try something like this...

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    08-20-2012
    Location
    Portland, Ore
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Need to Duplicate Data After Adding a Blank Row

    Sample 2.xlsSample 2.xlsHi Alpha,

    Thanks so much for sending this! It seemed to work, though it created a new row under the one I'd already created. Let me try explaining it again:

    1.) Attached is the workbook that I've edited with the following macros:

    Public Sub insert_row()
    Const TestColumn As Long = 1 'have taken column A as 1, thus B would be
    2 '
    Dim cRows As Long
    Dim i As Long
    cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row
    For i = cRows To 2 Step -1
    If Cells(i, TestColumn).Value <> IsNotBlank Then
    Cells(i, TestColumn).EntireRow.Insert
    End If
    Next i
    End Sub


    Sub move_secondary_contact()
    CalcMode = Application.Calculation
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    For i = 1 To (Range("A" & Rows.Count).End(xlUp).Row + 1)
    If Cells(i, 2) = "" Then
    Range("B" & i, Range("F" & i)).Value = _
    Range("G" & i - 1, Range("K" & i - 1)).Value
    Range("G" & i - 1, Range("K" & i - 1)).Value = ""
    End If
    Next i

    Application.ScreenUpdating = True
    Application.Calculation = CalcMode
    End Sub

    The sample is what it looks like after.

    In the highlighted yellow portions, how do I just copy what is in the cells with a value in the cells directly below them that are blank? This would sometimes need to be done for a phone number/fax number column, but I think I could create that part of the macro if these first parts were created.

    Thank you!

    -Ashley

  4. #4
    Registered User
    Join Date
    08-20-2012
    Location
    Portland, Ore
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Need to Duplicate Data After Adding a Blank Row

    2.) That was all.

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Need to Duplicate Data After Adding a Blank Row

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    08-20-2012
    Location
    Portland, Ore
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Need to Duplicate Data After Adding a Blank Row

    Thank you so much! That works great.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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