Excel Macro question

[jcroether]

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

 

[Bob Phillips]

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)

 

[jcroether]

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)










- 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?

 

[Bob Phillips]

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)

 

[jcroether]

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)








- 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.

 

[jcroether]

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.