Insert Formatted Row at specific point within Dynamic Range
 

I have a spreadsheet that has data from Columns A thru to K where th
number of records changes each time the user imports the data (in
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 Hol
or Other Business, the data is sorted (via a custom list) in thi
order. I want to then insert a heading row at the start of eac
category that contains the category heading with a border and shadin
that covers columns A thru to K.

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

Thanks
Kare

--
ks
-----------------------------------------------------------------------
ksp's Profile: http://www.excelforum.com/member.php...nfo&userid=626
View this thread: http://www.excelforum.com/showthread.php?threadid=54928

Insert Formatted Row at specific point within Dynamic Range
 

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

Insert Formatted Row at specific point within Dynamic Range
 

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

Insert Formatted Row at specific point within Dynamic Range
 

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

Insert Formatted Row at specific point within Dynamic Range
 

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 prett
and due to the nature of the data in the spreadsheet there wont be an
need for any charts/pivot tables etc

Thanks again

K

--
ks
-----------------------------------------------------------------------
ksp's Profile: http://www.excelforum.com/member.php...nfo&userid=626
View this thread: http://www.excelforum.com/showthread.php?threadid=54928