Insert Formatted Row at specific point within Dynamic Range

[ksp]

I have a spreadsheet that has data from Columns A thru to K where the number of records changes each time the user imports the data (in a named range called WIP_Area). In Column A each row has a ‘category’ which can be one of the following Work in Progress, Incoming, On Hold or Other Business, the data is sorted (via a custom list) in this order. I want to then insert a heading row at the start of each category that contains the category heading with a border and shading that covers columns A thru to K.

Does anyone know how I can easily find the start of each category to then insert a row?

Thanks
Karen

[FSt1]

hi,
try this...
Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Range("A2")

Do While Not IsEmpty(rng1)
Set rng2 = rng1.Offset(1, 0)
rng1.Select
If IsEmpty(rng2) Then
Cells(1, 1).Select
MsgBox ("Done")
Exit Sub
Else
If rng1.Value = rng2.Value Then
Set rng1 = rng2
Else
Range(Range("A1"), Range("A1").End(xlToRight)).Copy
rng2.EntireRow.Select
Selection.Insert Shift:=xlDown
Selection.PasteSpecial xlPasteAll
Set rng1 = rng2
End If
End If
Loop

regards
FSt1

"ksp" wrote:

>
> I have a spreadsheet that has data from Columns A thru to K where the
> number of records changes each time the user imports the data (in a
> named range called WIP_Area). In Column A each row has a ‘category’
> which can be one of the following Work in Progress, Incoming, On Hold
> or Other Business, the data is sorted (via a custom list) in this
> order. I want to then insert a heading row at the start of each
> category that contains the category heading with a border and shading
> that covers columns A thru to K.
>
> Does anyone know how I can easily find the start of each category to
> then insert a row?
>
> Thanks
> Karen
>
>
> --
> ksp
> ------------------------------------------------------------------------
> ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267
> View this thread: http://www.excelforum.com/showthread...hreadid=549286
>
>

[ksp]

Hi

It's certainly a step in the right direction. Your macro is creating a heading that is the same as what is in cell A1, I need the headings to change. Here's an example of what I mean:

Before Macro is Run Column A Looks something like this:

Work In Progress
Work In Progress
Work In Progress
Work In Progress
Incoming
Incoming
On Hold
On Hold
On Hold
Other Business
Other Business

After the Macro is Run I want it to look like this, where the blue text in italics are the headings that have been inserted:

Work In Progress
Work In Progress
Work In Progress
Work In Progress
Work In Progress
Incoming
Incoming
Incoming
On Hold
On Hold
On Hold
On Hold
Other Business
Other Business
Other Business

Any inspirational suggestions?

Thanks

KP

[Dave Peterson]

I find that even though this kind of thing makes the report look pretty, it
really makes other stuff more difficult--charts, subtotals (including count),
pivottables.

I wouldn't do it.

But if you really want...

Option Explicit
Sub testme()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
.Rows(1).Insert
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
'same category, do nothing
Else
.Rows(iRow).Insert
.Cells(iRow, "A").Value = .Cells(iRow + 1, "A").Value
With .Cells(iRow, "A")
With .Font
.Bold = True
.Italic = True
.ColorIndex = 3
End With
End With
End If
Next iRow
.Rows(1).Delete
End With
End Sub

By the way, since this is a plain text news group, your colors, italics don't
show up.

ksp wrote:
>
> Hi
>
> It's certainly a step in the right direction. Your macro is creating a
> heading that is the same as what is in cell A1, I need the headings to
> change. Here's an example of what I mean:
>
> Before Macro is Run Column A Looks something like this:
>
> Work In Progress
> Work In Progress
> Work In Progress
> Work In Progress
> Incoming
> Incoming
> On Hold
> On Hold
> On Hold
> Other Business
> Other Business
>
> After the Macro is Run I want it to look like this, where the blue text
> in italics are the headings that have been inserted:
>
> -Work In Progress-
> Work In Progress
> Work In Progress
> Work In Progress
> Work In Progress
> -Incoming-
> Incoming
> Incoming
> -On Hold-
> On Hold
> On Hold
> On Hold
> -Other Business-
> Other Business
> Other Business
>
> Any inspirational suggestions?
>
> Thanks
>
> KP
>
> --
> ksp
> ------------------------------------------------------------------------
> ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267
> View this thread: http://www.excelforum.com/showthread...hreadid=549286

--

Dave Peterson

[ksp]

Hi Dave

Thanks for that - it works great - really appreciate your help

I understand your point about making other things more difficult - fortunately the end users are concerned about making things look pretty and due to the nature of the data in the spreadsheet there wont be any need for any charts/pivot tables etc

Thanks again

KP