merge & centre - tricky Q

[KRK]

Hello,

This, I suspect, is a tricky question.

I want to merge & centre a piece of text across a number of columns. I know
how to do this, but...........

I want to select the columns 'dynamically' , ie controlled by data elsewhere
in the spreadsheet. The 'first' column and the 'last' column change
according to numbers elsewhere in the spreadsheet. The number of columns
varies, as does the start column. (sorry if I have over - described the
problem)

Is this possible ?

I'm not asking for a definitive solution at this stage, just some pointers &
ideas please?

Thanks

KK

 

[JLatham]

I believe you're going to need some VBA code to accomplish this. Probably as
a routine to be run automatically when the sheet is selected or when the
controlling data changes.

You're also going to be better off NOT using merge and center, instead
you'll want to use the coding equivalent of entering the text to be displayed
into the left most cell of the group and then selecting the group of cells on
the row and aligning them as if you'd used [Center Across Selection] from the
Format | Cells | Alignment | Horizontal Alignment choices in the Excel menu.
Trust me, any coding that has to be done in that area later will go much
smoother this way than by using Merge and Center.

 

[JLatham]

I know you didn't ask for a 'definitive' solution, and this isn't one - can't
provide that until we know more about the values that control the dynamic
range (where they are and what they represent). But here's a solution you
can play with.

Assumptions:
1) The label to be centered is going to be on row 4
2) there are other labels/data on row 5 and we want to center what's on row
4 over the used entries in row 5. So as you add/delete entries from the
right end of list in row 5, the centering of row 4 label/title adjusts
automatically.

This is a worksheet event level process. To put it into the proper place in
your workbook, open the workbook and select the sheet you want it to work
with. Then right-click on the worksheet's name tab and choose [View Code]
from the list that comes up. Copy and paste the code below into the empty
code module presented to you in the VB Editor. Change the row numbers if you
want, and even the text for the centered label (toward the end of the code)
and close the VB Editor.

Now make some entries on the row that controls the centering (row 5 as
coded) and you'll see the dynamic recentering of the title on row 4. As I
said, not a definitive solution - call it proof of concept for one special
setup.

Private Sub Worksheet_Change(ByVal Target As Range)
'checked any time a value changes on the worksheet

' row that has values/entries
'to control where label is centered
Const rowToTest = 5
Const centeredRow = 4 ' row we want to change
Dim startCol As Long
Dim endCol As Long

If Target.Row <> rowToTest Then
Exit Sub ' no work to do, no change in control row
End If
'find first column with an entry on the control row
If Not IsEmpty(Range("A" & rowToTest)) Then
'start column for centering is 1
startCol = 1
Else
startCol = Range("A" & rowToTest).End(xlToRight).Column
End If
'find last column with an entry in the control row
If Not IsEmpty(Range("A" & rowToTest).Offset(0, _
Columns.Count - 1)) Then
endCol = Columns.Count
Else
endCol = Range("A" & rowToTest).Offset(0, _
Columns.Count - 1).End(xlToLeft).Column
End If
'this assumes there is nothing in our row to be centered
'other than the label to be centered
'erase text and set alignment to left
Rows(centeredRow & ":" & centeredRow).ClearContents
Rows(centeredRow & ":" & centeredRow).HorizontalAlignment _
= xlLeft
Range(Cells(centeredRow, startCol).Address) = "My LABEL Text"
Range(Cells(centeredRow, startCol).Address & ":" & _
Cells(centeredRow, endCol).Address). _
HorizontalAlignment = xlCenterAcrossSelection

End Sub

 

[ShaneDevenshire]

Hi,

Here is a little code which might do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("A1", "A2")) Is Nothing Then
Range(Cells(5, [A1]), Cells(5, [A2])).UnMerge
Range(Cells(5, [A1]), Cells(5, [A2])).Merge
End If
End Sub

In this code I am assuming that row 5 is the row where you want to merge and
center.

 

[JLatham]

Shane,
I like it - but on my system, while it unmerged/merged properly based on the
column numbers entered into A1 and A2, it didn't center the text. The slight
modification I made (added 1 line) fixed that up for me:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("A1", "A2")) Is Nothing Then
Range(Cells(5, [A1]), Cells(5, [A2])).UnMerge
Range(Cells(5, [A1]), Cells(5, [A2])).Merge
Range(Cells(5, [A1]), Cells(5, [A2])).HorizontalAlignment = xlCenter
End If
End Sub

Hi,

Here is a little code which might do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("A1", "A2")) Is Nothing Then
Range(Cells(5, [A1]), Cells(5, [A2])).UnMerge
Range(Cells(5, [A1]), Cells(5, [A2])).Merge
End If
End Sub

In this code I am assuming that row 5 is the row where you want to merge and
center.
--
Cheers,
Shane Devenshire

Hello,

This, I suspect, is a tricky question.

I want to merge & centre a piece of text across a number of columns. I know
how to do this, but...........

I want to select the columns 'dynamically' , ie controlled by data elsewhere
in the spreadsheet. The 'first' column and the 'last' column change
according to numbers elsewhere in the spreadsheet. The number of columns
varies, as does the start column. (sorry if I have over - described the
problem)

Is this possible ?

I'm not asking for a definitive solution at this stage, just some pointers &
ideas please?

Thanks

KK

 

[ShaneDevenshire]

And that gives me another idea:

Private Sub Worksheet_Change(ByVal Target As Range)
Set R = Range(Cells(5, [A1]), Cells(5, [A2]))
On Error Resume Next
If Not Intersect(Target, Range("A1", "A2")) Is Nothing Then
With R
.UnMerge
.Merge
.HorizontalAlignment = xlCenter
End With
End If
End Sub

Of course we really should dim R
--
Cheers,
Shane Devenshire

Shane,
I like it - but on my system, while it unmerged/merged properly based on the
column numbers entered into A1 and A2, it didn't center the text. The slight
modification I made (added 1 line) fixed that up for me:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("A1", "A2")) Is Nothing Then
Range(Cells(5, [A1]), Cells(5, [A2])).UnMerge
Range(Cells(5, [A1]), Cells(5, [A2])).Merge
Range(Cells(5, [A1]), Cells(5, [A2])).HorizontalAlignment = xlCenter
End If
End Sub

Hi,

Here is a little code which might do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("A1", "A2")) Is Nothing Then
Range(Cells(5, [A1]), Cells(5, [A2])).UnMerge
Range(Cells(5, [A1]), Cells(5, [A2])).Merge
End If
End Sub

In this code I am assuming that row 5 is the row where you want to merge and
center.
--
Cheers,
Shane Devenshire

Hello,

This, I suspect, is a tricky question.

I want to merge & centre a piece of text across a number of columns. I know
how to do this, but...........

I want to select the columns 'dynamically' , ie controlled by data elsewhere
in the spreadsheet. The 'first' column and the 'last' column change
according to numbers elsewhere in the spreadsheet. The number of columns
varies, as does the start column. (sorry if I have over - described the
problem)

Is this possible ?

I'm not asking for a definitive solution at this stage, just some pointers &
ideas please?

Thanks

KK

 

[KRK]

Hello again

I seem to have started quite a debate.

Thanks for all the ideas, I will have to swot up on macros & VBA & stuff &
will get back to you all later

K