Insert blank rows conditionally

[hatatra]

I have an excel sheet that I need to clean up and "prettify". It's a sheet of scheduled shifts, spat out by a scheduling program, and it's a crapshoot. I've got the basics figured out, cleaning up whitespace, replacing short codes with more descriptive text, and I will use some conditional formatting... but what I can't figure out how to automate is adding blank rows if one is missing.

Basically the sheet has dates as columns, and employees and their shifts as rows. There is a row with an employee's name that has their scheduled hours, and the next row has their shift descriptor. The problem is that if an employee is absent the whole period, the sheet only has one row for them, and no shift descriptor row. This messes up alternate row formatting and makes reading the sheet harder. I need to add an empty row after each employee row, but only if there isn't already an empty row.

Can't install the XL2BB thingy on this machine, but here's a screencap with fake names. Up top are weekdays and day numbers (non-English). You can see here that "Tom Cruise" does not have shifts assigned, he's either free or away the whole period, and thus only has the one row, which then messes up the alternate row formatting for everyone below - the row with the name and hours is supposed to be bolded, not the one with shift descriptions, as you can see "Marilyn Monroe" has the wrong row bolded. The employee names are always in Column A.

There are some 60-70 employees and 21 days on each sheet, the number of employees varies. There's a new sheet every three weeks, so I'm hoping to have a macro or PowerQuery (or a combination) that would make this quick, preferably something I can just set up and tell another person to click this, this and that and save the resulting file... my current implementation is running the original sheet through PowerQuery for tidy-up and replacing text.

We have Office 365, Windows desktop.

 

[kevin9999]

Please try this on a copy of your workbook. I'm assuming Michael Jackson is in cell A2.

Sub hatatra()
    Dim i As Long
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1
        If Cells(i, 1) <> "" And Cells(i, 1).Offset(-1) <> "" Then Rows(i).EntireRow.Insert
    Next i
End Sub

 

[hatatra]

Thank you, this seems to have worked beautifully! Do I understand the logic correctly, the script checks if there's something in a cell on a row and the next (or previous?) row, and if so it inserts an empty row, then loops till end?

 

[kevin9999]

Do I understand the logic correctly

Correct