Merge Cells based on value of a cell 1

[cjc2797x]

I've scoured the internet for this, but haven't been able to find the code.

I would like to simply merge column A with column B based on a value from column C. So if column C has a 'Y' in it, merge column A to B, if it doesn't, skip the merge and move to the next row.

I've seen many examples of a merge, but all require hardcoded ranges.

Thanks for your help!

 

[ItIsHardToProgram]

Excel has its own function to do this, without even going through VBA.

Simply put an "IF" in your column D, it would go like this.

=IF(C1="Y"; A1 & B1;"")

Then you can drag it down to the extent of excels limit!

If there are multiple values that can trigger a merge in C, then you would have to put multiple if's.

Also, if there are too many values (5+) I suggest coding it in to a macro (there might be better ways that I do not know of.)



"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[cjc2797x]

Thanks for your response. The above formula example will concatenate A1 and B1 on the D1 column. But what I need is to actually merge the actual values of A1 and B1 without creating another column.

The only way I can think to do this is a macro that loops thru the values of column C and then performs the .merge based on the value of "Y".

 

[ItIsHardToProgram]

I am not sure why you "need" to concatenate without creating another column....

Take your column in D, replace A & B with D, this is what excel is meant to do...

If by merging you mean summing numbers, you can use A1 + B1 instead of A1 & B1...

If you require a specific merge, like certain value from A and certain value from B, then please specify

Maybe you could go furthermore into details?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[cjc2797x]

I think I'm close with the below code. What I want to is merge both A1 and B1 cells together based on the value of C1. So if C1 = "Y" then merge A1 and B1, otherwise skip to the next row.

My problem is the Range() function - I need to have the Range() parameters based on the current row in my for loop as it loops thru. All examples I've seen have hardcoded ranges.

Sub MergeCells()

lastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
Set MyRange = Range("C1:C" & lastRow)
For Each c In MyRange
If c.Value = "Y" Then
Range("A1:B1").Merge

End If
Next
End Sub

 

[Groves22]

This will do what you're asking for... but I don't really think it's what your asking for... let me know.

Sub MergeCells()

Dim lastrow As Long, MyRange As Range, c As Variant

lastrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
Set MyRange = Range("C1:C" & lastrow)

For Each c In MyRange
    If c.Value = "Y" Then
        Range("A" & c.Row & ":B" & c.Row).Merge
    End If
Next

End Sub

 

[ItIsHardToProgram]

Again, I don't think VBA is the best solution for what you need...

Can you answer my questions ,as it is very unclear what you are trying to achieve.

Not having macros is always easier for the common user to understand.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[cjc2797x]

Hi Groves22 - that is absolutely, exactly what I needed!!! Many, many thanks to you.

Hi ItIsHardToProgram - it's actually a report where group headers are merged above the detail. The data will change based on when the report is run, so the group headers can be anywhere on the sheet where column C = "Y". My program opens excel, pokes data into the cells and then calls the above macro to do some formatting. Makes it look pretty too

Thanks again Groves22

 

[ItIsHardToProgram]

Don't forget giving him a star!

It was unlcear to me that you were doing this programaticaly.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

I dislike address string concatenation...

For Each c In MyRange
  If c.Value = "Y" Then
    Range(Cells(c.row, "A"), Cells(c.row, "B")).Merge
  End If
Next

But generally, MERGE is not a very good idea, as it poses other problems down the road.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Groves22]

Hi Groves22 - that is absolutely, exactly what I needed!!! Many, many thanks to you.

Glad to help!