CODEWITHSUNDEEP
vbaexcel
Marcidee
Marcidee

Reputation: 17

Delete a blank row where the save value is above and below

I am a novice with VBA in Excel so need a little help please – I have a spreadsheet that runs for 3,000 rows plus. I am trying to place a blank line before every Monday (to divide each week). My spreadsheet contains numerous people who have several visits everyday – each person is separated from the next and I need a subtotal for each week and then a total for the month. I have a script that places a blank line above every Monday however Monday could appear on 4 consecutive rows, so I there need a script that will delete a row where Monday is above and below a blank row – any ideas?.

Sub Insert_Rows()
Dim i As Long

For i = 5000 To 1 Step -1
    If Cells(i, "A").Value Like "*Monday*" Then
        Cells(i, "A").EntireRow.Insert
    End If
Next i
End Sub

Aileen McCarthy Monday 25 04 16

Aileen McCarthy Monday 25 04 16

Aileen McCarthy Monday 25 04 16

Aileen McCarthy Monday 25 04 16

Aileen McCarthy Tuesday 26 04 16

Aileen McCarthy Tuesday 26 04 16

Aileen McCarthy Tuesday 26 04 16

Upvotes: 0

Views: 38

Answers (1)

Stadem
Stadem

Reputation: 423

Add an additional If statement to check the previous row:

For i = 28 To 1 Step -1
    If Cells(i, "A").Value Like "*Monday*" Then
        If i = 1 Then
            Cells(i, "A").EntireRow.Insert
        ElseIf Not Cells(i - 1, "A").Value Like "*Monday*" Then
            Cells(i, "A").EntireRow.Insert
        End If
    End If
Next i

Upvotes: 0

Related Questions