Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007
    Posts
    1

    Red face Unanswered: VBA Macro to autofill rows

    Hello,

    Wondering if anyone has some VBA codes that could do the following automatically:

    I have a spreadsheet with approx 250 unique rows of data. I need to insert 30 rows and fill in those rows with the same information for each of the 250 rows. For example:

    current data is

    1 CG CA Grain 5.5 35368.4
    1 CC CA Coal 8 54418.4
    ...........

    need to do the following:

    1 CG CA Grain 5.5 35368.4
    1 CG CA Grain 5.5 35368.4
    ...30 times...

    then start on the new row...

    1 CC CA Coal 8 54418.4
    1 CC CA Coal 8 54418.4
    ...30 times...

    and so on...

    Anything to help automate this process would be much appreciated.

    Thanks in advance for your help.
    David

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,475
    Provided Answers: 11
    the base way I leant was record a macro doing the job
    then look @ the code and tweak it
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  3. #3
    Join Date
    Mar 2006
    Posts
    163
    David

    You don't say where this data is located but you could try something like this.
    Code:
    Sub Do30()
    Dim rng As Range
        
        Set rng = Range("A1")
        While rng.Value <> ""
            rng.EntireRow.Copy
            rng.Offset(1).Resize(29).Insert
            Set rng = rng.Offset(30)
        Wend
        Application.CutCopyMode = False
        
    End Sub

Posting Permissions

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