Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Add X Rows Between Each List Item

  1. #1
    Join Date
    11th May 2006
    Posts
    101

    Add X Rows Between Each List Item

    i want to be able to drag a formula down and have it skip rows when doing so. please see example attached sheet for clarification. or if someone can suggest a way of inserting two rows inbetween data automatically that would work too.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    21st February 2006
    Location
    London, UK
    Posts
    3,831

    Re: Skip Two Rows In Formula

    I can't download your attachment right now but try selecting the first cell with the formula and the two below and then drag down those three cells with the Fill Handle. Does that work?
    Last edited by Dave Hawley; December 2nd, 2006 at 12:53.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    6th September 2004
    Posts
    438

    Re: Skip Two Rows In Formula

    Not quite sure if this is what you mean, but if you copy it down once then highlight the first 2 plus 2 blank rows you can then drag down.

    ie
    A1 1
    A2
    A3
    A4 2

    then highlight A1:A6 and drag down.


    TJ

    Edit: ignore this post as there is no need to do the first copy
    Last edited by tinyjack; December 2nd, 2006 at 05:53.
    Oh dear I need a beer
    Online Motorsport Game

  4. #4
    Join Date
    11th May 2006
    Posts
    101

    Re: Skip Two Rows In Formula

    unless i am doing something wrong excel tries to skip those cells when you drag or copy. maybe the solution is a macro that inserts two rows after data. perhaps someone could help with this idea.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Skip Two Rows In Formula

    Hi,

    Try something like

    VB:
    Sub Test() 
        Dim i   As Long, j  As Long 
        i = [a65536].End(xlUp).Row 
        For j = i To 2 Step -1 
            Rows(j + 1 & ":" & j + 2).Insert 
        Next j 
    End Sub 
    
    

  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    Re: Skip Two Rows In Formula

    This macro assumes you data starts in A1.

    VB:
    Sub AddRowsBetween() 
        Dim lLoop As Long 
         
        For lLoop = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1 
            Cells(lLoop, 1).Range("A1:A2").EntireRow.Insert 
        Next lLoop 
         
    End Sub 
    
    

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Set ComboBox To 1st List Item
    By outpatient in forum Excel General
    Replies: 1
    Last Post: May 21st, 2008, 00:04
  2. Add Combo Box Item To List Box
    By h in forum Excel General
    Replies: 12
    Last Post: December 3rd, 2007, 07:37
  3. Deselecting An Item In A List Box
    By johnmazz in forum Excel General
    Replies: 2
    Last Post: April 22nd, 2007, 00:08
  4. Validation List: Wide as Widest List Item
    By zura04 in forum Excel General
    Replies: 19
    Last Post: October 31st, 2006, 08:44
  5. List item upload
    By Raf in forum Excel General
    Replies: 10
    Last Post: July 18th, 2005, 18:47

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
  •  
porno