Solved

Excel macro

Posted on 2004-10-26
196 Views
Last Modified: 2012-06-27
I need to do the following inside an excel macro.  I will insert some information on row 2 of a spreadsheet.  I need that row copied and pasted on all even rows of that spreadsheet until the end of the recordset, starting on row 4.   Can anyone help with the code?

Thanks in advance,

IPT
0
Question by:iptrader
    2 Comments
     
    LVL 2

    Expert Comment

    by:aaarrrgggghhh
    Not quite sure what you mean by recordset, but the code below will copy row 2 into every even row of the spread sheet up until the last row, row 65536. Hope this helps you out.

    Dim i
    For i = 4 To 65536 Step 2
        Rows("2:2").Select
        Selection.Copy
        Rows(i & ":" & i).Select
        ActiveSheet.Paste
    Next i
    0
     
    LVL 3

    Accepted Solution

    by:
    These will run about 5x faster (or more) than the code posted above because calculations and screen updates are disabled...  Some display progress in the statusbar so you have an idea of when the macro will complete.

    '-------------------------------------
    ' Copy to entire spreadsheet (status bar updates)
    '-------------------------------------
    Sub CopyToAllRows()
        Dim i
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        For i = 4 To 65536 Step 2
            rows("2:2").Select
            Selection.Copy
            rows(i & ":" & i).Select
            If i Mod 1000 = 0 Then Application.StatusBar = "Processing Line #: " & i & "     " & "Percent Completed: " & Round((i / 65536) * 100, 2) & "%"
            ActiveSheet.Paste
        Next i
        Application.StatusBar = "Processing Completed!"
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
    End Sub

    '-------------------------------------
    ' Copy only to "used" rows (status bar updates)
    '-------------------------------------
    Sub CopyToUsedRows()
        Dim i
        Dim Rng As Range
        Application.Calculation = xlCalculationManual
        Application.ScreenUpdating = False
        Set Rng = ActiveSheet.UsedRange
        For i = 4 To Rng.rows.Count Step 2
            rows("2:2").Select
            Selection.Copy
            rows(i & ":" & i).Select
            If i Mod 1000 = 0 Then Application.StatusBar = "Processing Line #: " & i & "     " & "Percent Completed: " & Round((i / Rng.rows.Count) * 100, 2) & "%"
            ActiveSheet.Paste
        Next i
        Application.StatusBar = "Processing Completed!"
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
    End Sub

    '-------------------------------------
    ' Copy to entire spreadsheet (no statusbar updates = faster)
    '-------------------------------------
    Sub CopyToAllRowsNS()
        Dim i
        Application.Calculation = xlCalculationManual
        Application.ScreenUpdating = False
        For i = 4 To 65536 Step 2
            rows("2:2").Select
            Selection.Copy
            rows(i & ":" & i).Select
            ActiveSheet.Paste
        Next i
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
    End Sub

    '-------------------------------------
    ' Copy only to "used" rows (no statusbar updates = faster)
    '-------------------------------------
    Sub CopyToUsedRowsNS()
        Dim i
        Dim Rng As Range
        Application.Calculation = xlCalculationManual
        Application.ScreenUpdating = False
        Set Rng = ActiveSheet.UsedRange
        For i = 4 To Rng.rows.Count Step 2
            rows("2:2").Select
            Selection.Copy
            rows(i & ":" & i).Select
            ActiveSheet.Paste
        Next i
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
    End Sub
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Course: JavaScript Coding - Massive 12-Part Bundle

    Regardless of your programming skill level, you'll go from basics to advanced concepts in a vast array of JavaScript subjects including Sammy.js, Agility.js, Ember.js, Node.js, jQuery, AJAX, Extjs, AngularJS, Knockout.js, and JSON.

    Suggested Solutions

    Title # Comments Views Activity
    Help with my python script 6 114
    Order table with macro 3 50
    count8 challlenge 13 34
    object oriented javascript web form 8 25
    Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
    A short article about problems I had with the new location API and permissions in Marshmallow
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    877 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now