I have a spreadsheet that is about 1200 rows long. The first column contains
names of funds, and the other columns contain different information about
them. To extremely simplify what I'm trying to do, take the following list
for example.
A
A
B
B
B
C
D
D
This is what my first column looks like. I'd like to have Excel search
through and put blank rows between the dissimilar groups so that it looks
like the following
A
A

B
B
B

C

D
D
Any Ideas? This is going to take me a long time to do manually... and then
I want to make those blank rows color too, but doing that manually isn't
quite as bad. Thanks All

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

Application.ScreenUpdating = False

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 2 Step -1
If .Cells(i, TEST_COLUMN).Value <> .Cells(i - 1,
TEST_COLUMN).Value Then
.Rows(i).Insert
.Rows(i).Interior.ColorIndex = 38
End If
Next i

End With

Application.ScreenUpdating = True

End Sub




--
HTH

Bob

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

"Justin" <(E-Mail Removed)> wrote in message
news:E15ED28A-4D6C-44CC-B696-(E-Mail Removed)...
>I have a spreadsheet that is about 1200 rows long. The first column
>contains
> names of funds, and the other columns contain different information about
> them. To extremely simplify what I'm trying to do, take the following
> list
> for example.
> A
> A
> B
> B
> B
> C
> D
> D
> This is what my first column looks like. I'd like to have Excel search
> through and put blank rows between the dissimilar groups so that it looks
> like the following
> A
> A
>
> B
> B
> B
>
> C
>
> D
> D
> Any Ideas? This is going to take me a long time to do manually... and
> then
> I want to make those blank rows color too, but doing that manually isn't
> quite as bad. Thanks All

If you copy your column of names (with a heading) into another sheet,
you could then apply advanced filter to the column and obtain a unique
list of names. In your original sheet you can insert a new column A
and just copy the names from column B into the new column A. Then you
can apply the coloured background to the unique names (and a few other
columns) in the second sheet and copy them back to the original sheet
at the bottom of your data in column A. Then highlight all the data in
the orginal sheet (including the new column A) and do Data | Sort
using column A then column B as the sort fields. You can then delete
column A, and you can delete the worksheet with the unique names and
now you should have what you want.

Hope this helps.

Pete

On Jul 6, 7:10 pm, Justin <Jus...@discussions.microsoft.com> wrote:
> I have a spreadsheet that is about 1200 rows long. The first column contains
> names of funds, and the other columns contain different information about
> them. To extremely simplify what I'm trying to do, take the following list
> for example.
> A
> A
> B
> B
> B
> C
> D
> D
> This is what my first column looks like. I'd like to have Excel search
> through and put blank rows between the dissimilar groups so that it looks
> like the following
> A
> A
>
> B
> B
> B
>
> C
>
> D
> D
> Any Ideas? This is going to take me a long time to do manually... and then
> I want to make those blank rows color too, but doing that manually isn't
> quite as bad. Thanks All