Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Inserting rows after every week

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

    Default

    I have a list of consecutive dates running down column A in dd/mm/yy format. I am trying to insert 2 rows after every Sunday. My feeble attempt below completely failed, would appreciate any suggestions.

    Thanks

    Matt

    Dim irow As Long
    irow = 1

    Do While Cells(irow, 2) <> ""


    If Format(Range("A" & irow, "dddd")) = "Monday" Then
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    End If

    irow = irow + 1

    Loop

    End Sub


  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Matt


    I was going to write a Loop, but you will be flooded with these soon. Here is a much quicker method.

    Sub Doit()
    Dim rRange As Range
    Set rRange = Range("B8:B" & Range("A65536").End(xlUp).Row)

    rRange.FormulaR1C1 = "=IF(AND(WEEKDAY(RC[-1])=1,R[-7]C<>1),1)"
    rRange = rRange.Value
    rRange.SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Insert
    rRange.Clear

    End Sub



    Just ensure Column B is empty when you run it.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave you're a legend! Cheers

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

    Default

    Dave

    The code works great how would I amend it to insert 2 rows instead of 1?

    thanks

    Matt

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

    Default

    Ignore last post, have resolved

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oh well, better late than never.

    Sub Doit()
    Dim rRange As Range
    Set rRange = Range("B8:B" & Range("A65536").End(xlUp).Row)

    rRange.FormulaR1C1 = "=IF(AND(WEEKDAY(RC[-1])=1,R[-7]C<>1),1)"
    rRange = rRange.Value

    With rRange.SpecialCells(xlCellTypeConstants, xlNumbers)
    .EntireRow.Insert
    .EntireRow.Insert
    End With

    rRange.Clear

    End Sub

Some videos you may like

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
  •