CODEWITHSUNDEEP
excelvba
Marcidee
Marcidee

Reputation: 17

Delete a blank row below specific data

I need a script (Excel VBA) that will delete a blank line in a spreadsheet below wherever 'Monday' appears - can anyone help?

In the example below I need the Mondays to be in together (no blank rows)

name    Monday 02 05 16

name    Monday 02 05 16

name    Monday 02 05 16

name    Monday 02 05 16  
name    Tuesday 03 05 16  
name    Tuesday 03 05 16

Upvotes: 0

Views: 53

Answers (1)

user3598756
user3598756

Reputation: 29421

try this

Option Explicit

Sub MAIN()
Dim cell As Range
Dim mondaysAddress As String

With Worksheets("MyWS") '<~~ replace "MyWS" with you actual worksheet name    
    For Each cell In .Columns("A").SpecialCells(xlCellTypeConstants, xlTextValues)'<~~ replace "A" with whatever column cells you must search the word "Monday" in
        If InStr(UCase(cell.Value), "MONDAY") Then
            If IsEmpty(cell.Offset(1)) Then mondaysAddress = mondaysAddress & cell.Offset(1).Address & ","
        End If
    Next cell
    mondaysAddress = Left(mondaysAddress, Len(mondaysAddress) - 1)
    Range(mondaysAddress).EntireRow.Delete
End With

End Sub

Upvotes: 1

Related Questions