Macro to Make Headings

[katk]

I'm working on a spreadsheet that lists a lot of products from different regions. The regions are all listed in column A, and then the product names in B. I want to be able to work with the data without lots of awkward headings in the way but then I'd like to be able to run a macro that creates headings to separate the data by regions. I'm thinking I'd like it to: 1, look for region changes in column A and insert a blank row where it finds them; 2, insert the name of the new region into a cell in that blank row and then 3, bold and enlarge that text. I know how to do the first part (blank rows at region changes) but not the rest. Can anyone help me out with this one? Thanks!

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG22Mar05
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("A" & rows.Count).End(xlUp).row
[COLOR="Navy"]For[/COLOR] n = Lst To 1 [COLOR="Navy"]Step[/COLOR] -1
[COLOR="Navy"]With[/COLOR] Range("A" & n)
    [COLOR="Navy"]If[/COLOR] Not n = 1 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not .value = .Offset(-1).value [COLOR="Navy"]Then[/COLOR]
             .EntireRow.Insert
                [COLOR="Navy"]With[/COLOR] .Offset(-1)
                    .Font.Bold = True
                    .value = .Offset(1).value
                [COLOR="Navy"]End[/COLOR] With
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Else[/COLOR]
        .EntireRow.Insert
            [COLOR="Navy"]With[/COLOR] .Offset(-1)
                .Font.Bold = True
                .value = .Offset(1).value
            [COLOR="Navy"]End[/COLOR] With
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[katk]

Thanks so much, Mick!

 

[katk]

Hi Mick (or others?) --

I had another question that I was hoping you might be able to answer -- would it be possible for me to run a macro that would hide any row that had a value of "None" in the B column?

Thanks again for your previous help...

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG23Mar01
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("A" & rows.Count).End(xlUp).row
[COLOR="Navy"]For[/COLOR] n = Lst To 1 [COLOR="Navy"]Step[/COLOR] -1
[COLOR="Navy"]With[/COLOR] Range("A" & n)
    [COLOR="Navy"]If[/COLOR] .Offset(, 1) = "None" [COLOR="Navy"]Then[/COLOR]
       .EntireRow.Hidden = True
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[katk]

Again, thank you so much. I am continually blown away by the fact that people I don't know are willing to help me with my computer problems without asking for anything in return... it restores some of my faith in the world : )