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,424
    Provided Answers: 8
    the base way I leant was record a macro doing the job
    then look @ the code and tweak it
    hope this help

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  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
  •