Hello, I have a spreadsheet that is supposed to have four entries for
each name. More often than not, it has 1, 2, or 3. I need a macro
that will find like entries in a column, count them, and if they do
not equal four, I need enough blank rows to equal four. Is it
possible? Thanks! Example:

Have:

Doe, John
Eye, Pop
Eye, Pop
Finch, Atticus
Finch, Atticus
Finch, Atticus
Normal, Abby
Normal, Abby
Normal, Abby
Normal,
Abby


Need:
Doe, John



Eye, Pop
Eye, Pop


Finch, Atticus
Finch, Atticus
Finch, Atticus

Normal, Abby
Normal, Abby
Normal, Abby
Normal, Abby

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cRows As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 1 Step -1
cRows = Application.CountIf(.Columns(1), .Cells(i,
TEST_COLUMN).Value)
If cRows < 4 Then
.Rows(i + 1).Resize(4 - cRows).Insert
End If
i = i - cRows + 1
Next i

End With

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
ups.com...
Hello, I have a spreadsheet that is supposed to have four entries for
each name. More often than not, it has 1, 2, or 3. I need a macro
that will find like entries in a column, count them, and if they do
not equal four, I need enough blank rows to equal four. Is it
possible? Thanks! Example:

Have:

Doe, John
Eye, Pop
Eye, Pop
Finch, Atticus
Finch, Atticus
Finch, Atticus
Normal, Abby
Normal, Abby
Normal, Abby
Normal,
Abby


Need:
Doe, John



Eye, Pop
Eye, Pop


Finch, Atticus
Finch, Atticus
Finch, Atticus

Normal, Abby
Normal, Abby
Normal, Abby
Normal, Abby

On Apr 10, 5:32 pm, "Bob Phillips" wrote:
Yes. The problem is caused by NG wrap-around.

Replace

cRows = Application.CountIf(.Columns(1), .Cells(i,
TEST_COLUMN).Value)

with

cRows = Application.CountIf(.Columns(1), _
.Cells(i, TEST_COLUMN).Value)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message

oups.com...



On Apr 10, 6:13 am, "Bob Phillips" wrote:
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cRows As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 1 Step -1
cRows = Application.CountIf(.Columns(1), .Cells(i,
TEST_COLUMN).Value)
If cRows < 4 Then
.Rows(i + 1).Resize(4 - cRows).Insert
End If
i = i - cRows + 1
Next i

End With

End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

roups.com...

Hello, I have a spreadsheet that is supposed to have four entries for
each name. More often than not, it has 1, 2, or 3. I need amacro
that will find like entries in a column, count them, and if they do
not equal four, I need enough blank rows to equal four. Is it
possible? Thanks! Example:

Have:

Doe, John
Eye, Pop
Eye, Pop
Finch, Atticus
Finch, Atticus
Finch, Atticus
Normal, Abby
Normal, Abby
Normal, Abby
Normal,
Abby

Need:
Doe, John

Eye, Pop
Eye, Pop

Finch, Atticus
Finch, Atticus
Finch, Atticus

Normal, Abby
Normal, Abby
Normal, Abby
Normal, Abby- Hide quoted text -

- Show quoted text -

Thank you so much for your reply :) I ran the macro and (I am new to
this) it returned a compile syntax error with this line
highlighted. cRows =
Application.CountIf(.Columns(1), .Cells(i,
TEST_COLUMN).Value)

Is there something I can do?- Hide quoted text -

- Show quoted text -

Hi Again,

Thank you so much for your reply. I did make the change, but now the
macro doesn't stop running. If I stop it myself, it leaves me with
just one name.

On Apr 11, 11:57 am, wrote:
On Apr 10, 5:32 pm, "Bob Phillips" wrote:





Yes. The problem is caused by NG wrap-around.

Replace

cRows = Application.CountIf(.Columns(1), .Cells(i,
TEST_COLUMN).Value)

with

cRows = Application.CountIf(.Columns(1), _
.Cells(i, TEST_COLUMN).Value)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message

roups.com...

On Apr 10, 6:13 am, "Bob Phillips" wrote:
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cRows As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 1 Step -1
cRows = Application.CountIf(.Columns(1), .Cells(i,
TEST_COLUMN).Value)
If cRows < 4 Then
.Rows(i + 1).Resize(4 - cRows).Insert
End If
i = i - cRows + 1
Next i

End With

End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

roups.com...

Hello, I have a spreadsheet that is supposed to have four entries for
each name. More often than not, it has 1, 2, or 3. I need amacro
that will find like entries in a column, count them, and if they do
not equal four, I need enough blank rows to equal four. Is it
possible? Thanks! Example:

Have:

Doe, John
Eye, Pop
Eye, Pop
Finch, Atticus
Finch, Atticus
Finch, Atticus
Normal, Abby
Normal, Abby
Normal, Abby
Normal,
Abby

Need:
Doe, John

Eye, Pop
Eye, Pop

Finch, Atticus
Finch, Atticus
Finch, Atticus

Normal, Abby
Normal, Abby
Normal, Abby
Normal, Abby- Hide quoted text -

- Show quoted text -

Thank you so much for your reply :) I ran the macro and (I am new to
this) it returned a compile syntax error with this line
highlighted. cRows =
Application.CountIf(.Columns(1), .Cells(i,
TEST_COLUMN).Value)

Is there something I can do?- Hide quoted text -

- Show quoted text -

Hi Again,

Thank you so much for your reply. I did make the change, but now the
macro doesn't stop running. If I stop it myself, it leaves me with
just one name.- Hide quoted text -

- Show quoted text -

Hi again,

Sorry for the above. I reset the project and retried it and it works
perfectly. Thank you so very very much!!! You're the best. :) :) :)