Advanced formatting operation on a cell
 

Hi
could you please mail me with the sample file to

"Konrad Viltersten" wrote:

I understand this could be an advanced question so
potentially i might be willing to pay a small amount of
money if i get help.

I have a worksheet on the following form. A number of
fields contain, while the last column denotes a type
stretching accross several data rows. Something like:

dataA1 dataB1 dataC1 mark1
dataA2 dataB2 dataC2 mark1
dataA3 dataB3 dataC3 mark1
dataA4 dataB4 dataC4 mark2
dataA5 dataB5 dataC5 mark2
dataA6 dataB6 dataC6 mark3
dataA7 dataB7 dataC7 mark3
dataA8 dataB8 dataC8 mark3

Now, i can easily detect where a new type starts by
checking if the mark of the next row differs from the
current one.

The problem is that, after a change of mark, i would
like to perform the following.
1. Skip the contents of the second (and possibly the
third if it exists) row.
2. Merge the two first rows in the C-column
3. Make the contents of the first row centered (both
horizontally and vetically) and enlarge the font.

The result should be something like this:

dataA1 dataB1 dataC1 mark1
dataA2 dataB2 mark1
dataA3 dataB3 mark1
dataA4 dataB4 dataC4 mark2
dataA5 dataB5 mark2
dataA6 dataB6 dataC6 mark3
dataA7 dataB7 mark3
dataA8 dataB8 mark3

How do i do this?

--
Vänligen
Konrad
---------------------------------------------------

Sleep - thing used by ineffective people
as a substitute for coffee

Ambition - a poor excuse for not having
enough sence to be lazy
---------------------------------------------------

Advanced formatting operation on a cell
 

Konrad

The solution is trivial. However, in your example output, you lose the
values of C2,C3,C5, etc.

Is that intentional?

Advanced formatting operation on a cell
 

I understand this could be an advanced question so
potentially i might be willing to pay a small amount of
money if i get help.

I have a worksheet on the following form. A number of
fields contain, while the last column denotes a type
stretching accross several data rows. Something like:

dataA1 dataB1 dataC1 mark1
dataA2 dataB2 dataC2 mark1
dataA3 dataB3 dataC3 mark1
dataA4 dataB4 dataC4 mark2
dataA5 dataB5 dataC5 mark2
dataA6 dataB6 dataC6 mark3
dataA7 dataB7 dataC7 mark3
dataA8 dataB8 dataC8 mark3

Now, i can easily detect where a new type starts by
checking if the mark of the next row differs from the
current one.

The problem is that, after a change of mark, i would
like to perform the following.
1. Skip the contents of the second (and possibly the
third if it exists) row.
2. Merge the two first rows in the C-column
3. Make the contents of the first row centered (both
horizontally and vetically) and enlarge the font.

The result should be something like this:

dataA1 dataB1 dataC1 mark1
dataA2 dataB2 mark1
dataA3 dataB3 mark1
dataA4 dataB4 dataC4 mark2
dataA5 dataB5 mark2
dataA6 dataB6 dataC6 mark3
dataA7 dataB7 mark3
dataA8 dataB8 mark3

How do i do this?

--
Vänligen
Konrad
---------------------------------------------------

Sleep - thing used by ineffective people
as a substitute for coffee

Ambition - a poor excuse for not having
enough sence to be lazy
---------------------------------------------------

Advanced formatting operation on a cell
 

Konrad

Code follows. Work out bugs yourself.

To use it, mark your range and run the macro.

Public Sub MergeMarked()
Dim lStart As Long, lEnd As Long
Dim strLastMark As String, strMark As String
Dim rSelected As Range

Set rSelected = Selection
lStart = 1
strLastMark = rSelected.Cells(1, rSelected.Columns.Count).Value
While lStart < rSelected.Rows.Count
lEnd = lStart
While strLastMark = rSelected.Cells(lEnd,
rSelected.Columns.Count).Value And _
rSelected.Cells(lEnd, rSelected.Columns.Count).Value < ""
If rSelected.Cells(lEnd, rSelected.Columns.Count).Value =
"" Then
GoTo AllDone
End If
lEnd = lEnd + 1
Wend
If lEnd lStart Then
rSelected.Range(Cells(lStart + 1, rSelected.Columns.Count -
1), Cells(lEnd, rSelected.Columns.Count)).Clear
rSelected.Cells(lStart + 1, rSelected.Columns.Count -
1).Value = strLastMark
rSelected.Range(Cells(lStart + 1, rSelected.Columns.Count -
1), Cells(lEnd, rSelected.Columns.Count)).Merge
rSelected.Cells(lStart + 1, rSelected.Columns.Count -
1).HorizontalAlignment = xlHAlignCenter
rSelected.Cells(lStart + 1, rSelected.Columns.Count -
1).VerticalAlignment = xlVAlignCenter
lStart = lEnd
End If
lStart = lStart + 1
strLastMark = rSelected.Cells(lStart,
rSelected.Columns.Count).Value
Wend

AllDone:

End Sub

Advanced formatting operation on a cell
 

The solution is trivial. However, in your example output,
you lose the values of C2,C3,C5, etc. Is that intentional?

Yes, it is. Actually, the contents i loose are only a copies
of the ones i keep. In fact, i lied to you - the C-column
_IS_ the marker (it was easier to explain, i guess). Sorry
for misusing your thrust, hehe. :)

I'll try your solution and we'll see how well i can get it
to work. By the way, in case in does (which i believe it
will, of course) - i did mention an economic side of a
"thanks". If you wish to check how truthful that was,
drop me a line. Thanks for the help!

--
Vänligen
Konrad
---------------------------------------------------

Sleep - thing used by ineffective people
as a substitute for coffee

Ambition - a poor excuse for not having
enough sence to be lazy
---------------------------------------------------

Advanced formatting operation on a cell
 

could you please mail me with the sample file
to

Yes, i can.
....but why? Unless you study Japanese at an
intermediate level, the file will do you no good. :)

--
Vänligen
Konrad
---------------------------------------------------

Sleep - thing used by ineffective people
as a substitute for coffee

Ambition - a poor excuse for not having
enough sence to be lazy
---------------------------------------------------