Results 1 to 8 of 8

Need VBA Macro to Copy and paste column data in blank rows and repeat through spreadsheet

Threaded View

slk1186 Need VBA Macro to Copy and... 08-07-2014, 11:14 AM
mikeTRON Re: Need VBA Macro to Copy... 08-07-2014, 11:21 AM
slk1186 Re: Need VBA Macro to Copy... 08-07-2014, 12:14 PM
jaslake Re: Need VBA Macro to Copy... 08-07-2014, 11:32 AM
mikeTRON Re: Need VBA Macro to Copy... 08-07-2014, 12:28 PM
slk1186 Re: Need VBA Macro to Copy... 08-07-2014, 12:35 PM
mikeTRON Re: Need VBA Macro to Copy... 08-07-2014, 12:49 PM
slk1186 Re: Need VBA Macro to Copy... 08-07-2014, 12:50 PM
  1. #1
    Registered User
    Join Date
    07-31-2014
    Location
    chicago
    MS-Off Ver
    2010
    Posts
    6

    Need VBA Macro to Copy and paste column data in blank rows and repeat through spreadsheet

    Hi All,

    Im a bit of a n00b with these Macros and have been trying to modify some VBA code I used for a different but similar task without success. What I want to do is ensure that there are no blank cells in the the first 2 columns of my spreadsheet.

    The data is stored like this

    AA : 12 : 34 : 45
    : : : 46
    : : : 47
    BB : 23 : 34 : 48
    : : : 49
    CC : 45 : 34 : 50
    DD: 67 : 34 : 51
    : : : 52
    : : :53
    EE: 91 : 34 :54

    And id like the Macro to run through the sheet a copy columns 1 and 2 and paste them into proceeding rows until they hit the next set of data, then copy those cells and paste until they hit the next and so on, so the sheet looks like this

    AA : 12 : 34 : 45
    AA : 12 : : 46
    AA : 12 : : 47
    BB : 23 : 34 : 48
    BB : 23 : : 49
    CC : 45 : 34: 50
    DD: 67 : 34 : 51
    DD: 67 : : 52
    DD: 67: :53
    EE: 91 : :54

    Sorry for the shitty drawing but let me know if you have any ideas. This is what I tried to no avail

    
    Sub AddBlankLines()
    
        Dim Col As Variant
        Dim BlankRows As Long
        Dim LastRow As Long
        Dim R As Long
        Dim StartRow As Long
    
            Col = "A"
            StartRow = 1
            BlankRows = 2
        
                LastRow = Cells(Rows.Count, Col).End(xlUp).Row
                
                Application.ScreenUpdating = False
                
                With ActiveSheet
                    For R = LastRow To StartRow + 1 Step -1
                        If .Cells(R + 1, Col) = "" Then
                            .Cells(R, Col).Copy
                            .Cells(R + 1, Col).Paste Shift:=xlDown
                        End If
                    Next R
                End With
      
                Application.ScreenUpdating = True
                
    End Sub
    Last edited by slk1186; 08-07-2014 at 12:06 PM. Reason: solved

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Copy data and paste in first blank row of Column A
    By Hood in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-08-2014, 08:48 AM
  2. How to create a macro to insert blank rows and copy data into blank rows?
    By zodiack101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-29-2013, 01:18 PM
  3. [SOLVED] Macro to copy columns and paste into one column in next blank column
    By rmmohan in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-31-2013, 03:14 PM
  4. [SOLVED] copy everything from column B to end of data, paste to summary, repeat for next worksheet
    By fabrecass in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-12-2013, 07:43 AM
  5. Macro - Copy/Paste certain cells and move down column and repeat
    By rohan87 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-16-2013, 02:02 AM

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