Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Hello All,
    Is there an easy way I can insert blank rows between rows of text in Excel 2003. For example if my worksheet looks like this

    Text
    Text
    Text
    Text

    I want a blank row after each row of text so it looks like the example below without having to manually add a new row.
    Text

    Text

    Text

    Text

    Text

    Thanks in advance for any assistance you can provide
    MOState

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Let's say the text is in column A. You could run the following macro:

    Code:
    Sub InsertRows()
      Dim r As Long
      Dim m As Long
      m = Range("A" & Rows.Count).End(xlUp).Row
      For r = m To 2 Step -1
    	Range("A" & r).EntireRow.Insert
      Next r
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    You are wonderful, it works like a charm!!

    Thanks so much for the quick reply, Hans!!!
    MOState

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='786599' date='28-Jul-2009 22:10']Let's say the text is in column A. You could run the following macro:

    Code:
    Sub InsertRows()
      Dim r As Long
      Dim m As Long
      m = Range("A" & Rows.Count).End(xlUp).Row
      For r = m To 2 Step -1
    	Range("A" & r).EntireRow.Insert
      Next r
    End Sub
    [/quote]

    Hi Hans

    As you know I am 'Code Challenged' but I try to read every reply and see if I can understand how a solution was arrived at. Looking at your code above which part of this says count down 5 rows and insert a new row. I understand that M is the range and I think that R is the rows so 'For r = m To 2 Step -1' is I guess where it is worked out, but how, it does not say go down 5 rows and insert a mew row?

    Thanks
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The code doesn't count down 5 rows. It inserts a blank row between all existing rows.

    The line

    m = Range("A" & Rows.Count).End(xlUp).Row

    calculates the row number of the last non-blank cell in column A:

    Range("A" & Rows.Count) is the last cell in column A (for Excel 97-2003 it is cell A65536, for Excel 2007 it is cell A1048576).
    .End(xlUp) moves upwards from the last cell and stops when it encounters a non-blank cell, i.e. at the last non-blank cell in column A.
    .Row returns the row number of this cell.

    The loop

    For r = m To 2 Step -1
    ...
    Next r

    lets r vary backwards from m (the last occupied row) up to 2. At each step of the loop, the line

    Range("A" & r).EntireRow.Insert

    inserts a blank row.

    I have attached a small sample workbook where the loop is executed step by step, so that you can see how it works. Click the button on the worksheet to start the macro. (You must enable macros of course)
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you very much Hans

    I will go and experiment with it now
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='MOSTATE' post='786596' date='29-Jul-2009 05:03']Hello All,
    Is there an easy way I can insert blank rows between rows of text in Excel 2003. For example if my worksheet looks like this

    Text
    Text
    Text
    Text

    I want a blank row after each row of text so it looks like the example below without having to manually add a new row.
    Text

    Text

    Text

    Text

    Text

    Thanks in advance for any assistance you can provide
    MOState[/quote]

    Hi

    perhap this without a macro

    assuming A2 is where your data start. in B2, type 1
    and drag down, select Fill Series. Excel will number your rows as 1,2,3 etc.
    Select B2 to end of your series numbers end, do a copy and in the first empty cell in col B just after the last
    series number end, do a paste.
    Highlight the whole range from A2 to end of col B cell where the data end
    Now sort all of your data on that column and you will have empty rows between everything
    If you want to insert more than one row, just paste the copied data that many times
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

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