Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert Blank Line Before data (2000 sp3)

    Greetings!
    It's been a few weeks since my last post. So I thought I would pose another issue for you all.

    I have a large spreadsheet configured to have data in toe following manner.

    First line contains data respective to ownership of information. Second thru 'n' is information to be worked.

    The data was imported from a text file, and all rows are without separation. What I need is to have a method of
    structuring down through the data, using Column A as the 'key'. If the data in column 'A' is equal to or less than six digits, insert a blank line
    above the row. Also, change the cell color to black for the inserted row.

    Attached is a sample file.

    Thanks,
    Brad

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Blank Line Before data (2000 sp3)

    Does this do what you want?

    <code>
    Public Sub SepData()
    Dim lMax As Long, I As Long
    With Worksheets("brad_ca_iw_part1")
    lMax = .Range("A65536").End(xlUp).Row - 1
    For I = lMax To 3 Step -1
    If Len(.Range("A1").Offset(I, 0).Value) <= 6 Then
    .Range("A1").Offset(I, 0).EntireRow.Insert
    Range(.Range("A1").Offset(I, 0), .Range("G1").Offset(I, 0)).Interior.ColorIndex = 1
    End If
    Next I
    End With
    End Sub
    </code>
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Blank Line Before data (2000 sp3)

    Legare,
    Worked great!


    Thanks,
    Brad

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Blank Line Before data (2000 sp3)

    Legare,

    Just had another thought. I suspect that some of the users of the data will want to sort based upon Column 'F'. However, the data is not condusive to sorting and keeping the information together.

    Prior to inserting the blank roe, is it possible to grab the contents of the cell 'F1 thru F(n), fill down, until it finds an occupied cell, then grab that cell and fill down? etc...

    Such that

    F3 is occupied with NO BUSINESS SEGEMENT, F4 is empty, F5 is occupied . Etc... There is not a defined method of each group of data to be clustered.

    Any help would be great. If you need more, please let me know if this is not clear.

    Brad

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Blank Line Before data (2000 sp3)

    If I understand, this should do it:

    <code>
    Public Sub SepData()
    Dim lMax As Long, I As Long
    With Worksheets("brad_ca_iw_part1")
    lMax = .Range("A65536").End(xlUp).Row - 1
    For I = 2 To lMax
    If .Range("F1").Offset(I, 0).Value <> "" Then
    .Range("F1").Offset(I, 0).Copy
    Else
    .Paste Destination:=.Range("F1").Offset(I, 0)
    End If
    Next I
    For I = lMax To 3 Step -1
    If Len(.Range("A1").Offset(I, 0).Value) <= 6 Then
    .Range("A1").Offset(I, 0).EntireRow.Insert
    Range(.Range("A1").Offset(I, 0), .Range("G1").Offset(I, 0)).Interior.ColorIndex = 1
    End If
    Next I
    End With
    End Sub
    </code>
    Legare Coleman

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Blank Line Before data (2000 sp3)

    Legare,

    Thanks! Work fine...


    Brad

Posting Permissions

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