Need Help With Row Insert Macro in Excel for Mac 16.16.8

Joannie

New member
Joined
May 22, 2019
Messages
2
Reaction score
0
Points
0
Excel Version(s)
16.16.8 Mac
I have a ponderous job where I need to insert a blank row between names on a spreadsheet. (It's not even, or every other row). I'll get a list of 200 or so names like this:

Bob Smith $10
Bob Smith $34
Carry Smith $10
Dan Wolker $15
Dan Wolker $20
Dan Wolker $50
Dan Wolker $90
Evelyn Smith $10
Jack Jones $10
Mary Walsh $56
Mary Walsh $54
Mary Walsh $50
Mary Walsh $95
Pat Major $10

I need a blank row so I can add up what we owe them.
I can select the row with my mouse (right hand) but I'd love a remedy to insert a row with my left hand - like using an F key, or even "qq" to trigger a row insert. I just want to work faster. I know I can select the row, right click and get a menu and hit "insert" but I've been known (in the heat of the battle) to hit "delete" instead. :dance:
It's a ponderous chore and I want it over with as fast as possible.
Any ideas? Thank you so much!!!!
Joannie
 
Here is a VBA solution that will do what you asked. Assumes your data is in columns A and B

Code:
Option Explicit


Sub Blanks()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = lr To 2 Step -1
        If Range("A" & i) <> Range("A" & i - 1) Then
            Range("A" & i).EntireRow.Insert
        End If
    Next i
End Sub
 
Holy cow!

I am screaming very loud. (Luckily, I'm working from home.)
I cannot begin to fathom this - it works PERFECTLY and I have just decided to keep this job since this is no longer my special headache.
I cannot thank you enough!!!! Brilliant!!!!!:lock1:








Here is a VBA solution that will do what you asked. Assumes your data is in columns A and B

Code:
Option Explicit


Sub Blanks()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = lr To 2 Step -1
        If Range("A" & i) <> Range("A" & i - 1) Then
            Range("A" & i).EntireRow.Insert
        End If
    Next i
End Sub
 
Back
Top