Help: how to insert 2 blank cells in between the rows until
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Help: how to insert 2 blank cells in between the rows until

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    99
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    WORD
    abase
    abash
    abbess
    abbey
    abbot
    abdicate
    .
    .
    .
    Thank you in advance!

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    You might want to try the following:

    Range("L20").Select
    Do While Range("L20").Offset(1, 0).Value <> ""
    Range("L" & Range("L20").End(xlDown).Row & ":L" & Range("L20").End(xlDown).Row + 1).Insert Shift:=xlDown
    Loop

    It is working from bottom up, and the above would only work if you data column if continuous (ie. no empty cells in between). And "L20" is where the start cell is.

    And if you have data in the other cells/columns, and you want to shift whem down as well, then use:

    Range("L" & Range("L20").End(xlDown).Row & ":L" & Range("L20").End(xlDown).Row + 1).EntireRow.Insert

    instead of the line above.

    Hope it helps

  3. #3

    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Alternatively :-

    Dim rng As Range, c As Long
    Application.ScreenUpdating = False
    Columns(1).Insert
    Set rng = Range([B1], [B65536].End(xlUp)).Offset(0, -1)
    c = rng.Cells.Count
    With rng
    .Cells(1, 1).Value = 1
    .Cells(1, 1).AutoFill Destination:=rng, Type:=xlFillSeries
    .Copy .Offset(c, 0).Resize(c + c, 1)
    Rows("1:" & c * 3).Sort Key1:=Range("A1")
    .EntireColumn.Delete
    End With


    [ This Message was edited by: Abdc on 2002-03-21 16:25 ]

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    99
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    99
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    eom

    [ This Message was edited by: trillicomm on 2002-03-21 17:52 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Try the following, and see where that's what you need:

    Range("A2").Select
    Row_Numb = Range("A2").End(xlDown).Row
    For i = Row_Numb To 2 Step -1
    Rows(i).Copy
    Rows(i & ":" & i + 1).Insert shift:=xlDown
    Next i

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com