Solved: Merger and Unmerge cells

[austenr]

How can i combine these two bits of code so that when you put it in a worksheet selection change event it works correctly? 1st bit works correctly but to place the cell contents in and out of the cell depending on the condition you have to merge and unmerge the range in the second bit to get it to insert.

Select Case Sheets("Input + Wksheet").Range("B13")
Case "Y"
Sheets("Direct Bill ONLY").Range("c48") = Sheets("Input + Wksheet").Range("A161")
Case "N"
Sheets("Direct Bill ONLY").Range("c48") = Sheets("Input + Wksheet").Range("A163")
End Select

this is a recorded macro:

Range("C48:U48").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.UnMerge

With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.Merge
End Sub

[GTO]

Hi Austen,

I hope you won't mind, but this has been bugging me a bit. I do not recall when I started the salutory with your name, but (maybe just my pea/blonde) brain, recently started questioning whether I was calling you by your given or surname.

I suppose the reason is that at my work, my username is stumpm.

Anyways, if you don't mind, is your first name Austen?

As to your question I am afraid I have another in return my friend. I tried this:

In Sheet2: Merge cells A2:B1 and merge cells A2:B2.

In Sheet3: place vals in A1 and A2


Option Explicit

Sub AddVal()
Sheet2.Range("A1").Value = Sheet3.Range("A1")
Sheet2.Range("A2:B2").Value = Sheet3.Range("A2").Value
End Sub

Sub ClearVal()
Sheet2.Range("A1").ClearContents '<fails
Sheet2.Range("A2:B2").ClearContents
End Sub

I can place vals in the merged cells by either referencing the leftmost(/and I believe uppermost) cell, or by referencing the merged area.

I do see that I need to include all the cells merged to delete/empty a val.

Have you tried this? I am not seeing where unmerging and re-merging the cells is necessary.

Thanks,

Mark

[austenr]

Yes Mark, that is my surname Austen. Most spell it with an "i", ie. Austin. Although I did see it once spelled Austan. Oh well, back to the issue.

I will try to give your suggestion a try. Ill let you know the outcome. Thanks.

[GTO]

I would rather have friends call me Mark vs Stump, what do you like?

[austenr]

That will work. Thanks.

[austenr]

hmmm....

The "N" part works but if I change it to "Y", the text doesnt change.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Sheets("Input + Wksheet").Range("B13")
Case "Y"
Sheets("Direct Bill ONLY").Range("c48:U48").ClearContents
Sheets("Direct Bill ONLY").Range("c48") = Sheets("Input + Wksheet").Range("A161")
Case "N"
Sheets("Direct Bill ONLY").Range("c48:U48").ClearContents
Sheets("Direct Bill ONLY").Range("c48") = Sheets("Input + Wksheet").Range("A163")
End Select
End Sub

[GTO]

Sorry Austen, I am fading a bit. What sheet is this under, "Sheets("Input + Wksheet")" ?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

[austenr]

Sheet("Input + Wksheet")

[austenr]

figured it out. Had the code in the wrong sheet duh!!!!!!! :banghead::banghead::banghead:

[GTO]

Barely tested and I'm 'coma-status'.

Try:


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(False, False) = "B13" Then
Select Case UCase(Target.Value)
Case "Y"
Sheets("Direct Bill ONLY").Range("c48:U48").ClearContents
Sheets("Direct Bill ONLY").Range("c48") = Sheets("Input + Wksheet").Range("A161")
Case "N"
Sheets("Direct Bill ONLY").Range("c48:U48").ClearContents
Sheets("Direct Bill ONLY").Range("c48") = Sheets("Input + Wksheet").Range("A163")
End Select
End If
End Sub

For brevity's sakes, I see no reason to run the code ea time you move about the sheet (granted, the Change event will be called). I think we only need to run if B13 has changed, and probably, changed alone (vs. clearing a bunch of cells, as we are only enacting a change if B13 is one of two values).