+ Reply to Thread
Results 1 to 7 of 7

Adding a space between rows depending on data

  1. #1
    Registered User
    Join Date
    10-14-2013
    Location
    West Virginia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Adding a space between rows depending on data

    Hi, can someone tell me how to do the following...

    I have a spreadsheet with thousands of rows. One of the columns (Column Q) is a Date of Service column and the spreadsheet is ordered from the oldest date of service to the newest. I want to add a space between each new date of service. The problem is that there may be 10 rows with the date of service of 01/01/12, then 3 rows with the date of service 01/02/12, and then 15 rows with the date of service 01/03/12. In this example, I would want a space between the last 01/01/12 dos and the first 01/02/12 date of service, and then a space between the last 01/02/12 date of service and the first 01/03/12 date of service, and so on.

    Thanks

  2. #2
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,377

    Re: Adding a space between rows depending on data

    Insert this macro into a module

    Sub TestCompare()
    Dim Rng As Range
    Dim c As Range
    Set Rng = Range("Q1:Q" & Range("Q65536").End(xlUp).Row)
    For Each c In Rng
    If Not c.Value = "" Then
    If Not c.Value = c.Offset(1, 0).Value Then
    c.Offset(1, 0).Rows.Insert Shift:=xlDown
    End If
    End If
    Next c
    End Sub
    Blake 7

    If your question has been answered, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

    If any member's response has helped to solve your problem, please consider using the scales icon on the top right of their post to show your appreciation.

    http://failblog.org/

  3. #3
    Registered User
    Join Date
    10-14-2013
    Location
    West Virginia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Adding a space between rows depending on data

    That works but it didn't do exactly what I needed which I probably didn't explain well enough. That macro does insert the spaces as needed. But I need the spaces to apply to the entire row, not just column Q. As is, the Macro inserts the space correctly in column Q but all of the other columns remain as is.

  4. #4
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,377

    Re: Adding a space between rows depending on data

    Add this code to a module

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    10-14-2013
    Location
    West Virginia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Adding a space between rows depending on data

    I tried the new code but it didn't appear to make any changes. There were no spaces added in any of the columns. Any ideas?

  6. #6
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,377

    Re: Adding a space between rows depending on data

    works fine for me - insert into a module then run macro

  7. #7
    Registered User
    Join Date
    10-14-2013
    Location
    West Virginia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Adding a space between rows depending on data

    Blake 7, I appreciate your help but I was never able to get your code to work with my spreadsheet.

    However, I did find a code that worked for me. I'm going to post it here in case anyone else ever sees this thread or needs it. This is assuming the date is in Column Q:

    Sub InsBl()
    Dim LR As Long, i As Long
    LR = Range("Q" & Rows.Count).End(xlUp).Row
    For i = LR To 2 Step -1
    If Range("Q" & i).Value <> Range("Q" & i - 1).Value Then Rows(i).Insert
    Next i
    End Sub

    Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Adding space between rows.
    By amirshaneh in forum Excel General
    Replies: 2
    Last Post: 02-15-2011, 05:58 AM
  2. Adding rows depending on user input
    By haitham1984 in forum Excel General
    Replies: 0
    Last Post: 11-10-2008, 07:30 AM
  3. Adding Rows depending on user input
    By haitham1984 in forum Excel General
    Replies: 0
    Last Post: 11-09-2008, 02:35 AM
  4. adding space in between rows
    By back_lash in forum Excel General
    Replies: 3
    Last Post: 12-28-2007, 03:08 PM
  5. Adding Row Values depending on Corresponding Rows
    By chr5648 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-13-2007, 05:26 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1