MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Make bold & Insert blank space


Posted by Jeremy on March 08, 2000 2:40 AM

The challenge,

I have 7 columns of data and the total row length can vary.
I need to separate the data as are all bunched together.
Each unit of data is 9 rows in length. I would like a
Macro that can separate the lines at 9 row intervals.
Then make Bold the text in the A column of the first row of data.
I have 3 rows at the beginning that I don't want touched.

Can any one help???

Eg. Text as is:

Sales Accounts for Financial Services
NOT TO BE SHOWN OUTSIDE ~~~~~~~~
Sales Account #### & ######
Week Start Total Local National IDD To Mobile Other
19/02/00 467977 256242 159172 2583 43233 6747
12/02/00 800803 485891 238155 6989 61201 8567
05/02/00 712919 375585 252671 11197 63887 9579
29/01/00 747457 384179 276237 14306 64699 8036
22/01/00 1202411 467450 637818 12219 75562 9362
15/01/00 1328944 537422 687942 13167 80411 10002
08/01/00 1287447 607742 570937 11851 85344 11573
01/01/00 982289 479896 422418 9171 59253 11551
Sales Account ########
Week Start Total Local National IDD To Mobile Other
19/02/00 4250827 2161649 1796865 10773 260634 20906
12/02/00 4492682 2272409 1907975 10811 279964 21523
05/02/00 4451416 2270322 1872058 11370 276392 21274
29/01/00 4538266 2287038 1936554 11732 279372 23570
22/01/00 4517144 2285853 1919965 12030 276486 22810
15/01/00 4465816 2275422 1887710 10201 270951 21532
08/01/00 4514385 2307782 1910379 9861 265027 21336
01/01/00 3594851 1845192 1532937 9343 191467 15912

I would like it to look like this (Sales account bit Bold);

Sales Accounts for Financial Services
NOT TO BE SHOWN OUTSIDE ~~~~~~~~~~

Sales Account ###########
Week Start Total Local National IDD To Mobile Other
19/02/00 467977 256242 159172 2583 43233 6747
12/02/00 800803 485891 238155 6989 61201 8567
05/02/00 712919 375585 252671 11197 63887 9579
29/01/00 747457 384179 276237 14306 64699 8036
22/01/00 1202411 467450 637818 12219 75562 9362
15/01/00 1328944 537422 687942 13167 80411 10002
08/01/00 1287447 607742 570937 11851 85344 11573
01/01/00 982289 479896 422418 9171 59253 11551

Sales Account #############
Week Start Total Local National IDD To Mobile Other
19/02/00 4250827 2161649 1796865 10773 260634 20906
12/02/00 4492682 2272409 1907975 10811 279964 21523
05/02/00 4451416 2270322 1872058 11370 276392 21274
29/01/00 4538266 2287038 1936554 11732 279372 23570
22/01/00 4517144 2285853 1919965 12030 276486 22810
15/01/00 4465816 2275422 1887710 10201 270951 21532
08/01/00 4514385 2307782 1910379 9861 265027 21336
01/01/00 3594851 1845192 1532937 9343 191467 15912


Posted by jpena on March 08, 2000 7:06 AM

Hi,

I think this is what u want. Just select the first cell in column A after those three first rows u don't want to modify. I guess that would be the first Sales Account #########.

Sub formatdata()
Dim i As Integer
Dim j As Integer

i = 10
j = 0

ActiveCell.EntireRow.Font.Bold = True

Do Until IsEmpty(ActiveCell.Offset(i + j, 0))

With ActiveCell.Offset(i + j, 0)
.EntireRow.Font.Bold = True
.EntireRow.Insert

End With
j = j + 1
i = i + 10

Loop
End Sub


Bye :^