Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel macro

Posted on 2004-10-26
2
Medium Priority
?
198 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
Comment
Question by:iptrader
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 2

Expert Comment

by:aaarrrgggghhh
ID: 12419464
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:
elantra earned 2000 total points
ID: 12424606
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
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…

610 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