CODEWITHSUNDEEP
vbaexcel
tehcoder
tehcoder

Reputation: 25

Merging excel cells isn't works how its supposed to

mycode :- 

Public Sub CombineCells()
  'Use to mash all cells with there contents into one

  Dim selectedCells As Range
  Dim cell As Range
  Dim cellText As String

  Application.DisplayAlerts = False
  cellText = ""

  Set selectedCells = Selection
  For Each cell In selectedCells
    cellText = cellText & cell.Value & " "
  Next

  selectedCells.merge
  selectedCells.Value = Trim(cellText)
  selectedCells.WrapText = True
  Application.DisplayAlerts = True

End Sub

Basically I want to merge cells from A1 to H6, Range A1:H6, into the same cell without losing the data in the cells (they are going to have the same number in every cell((like same value/)) when I run my code, it saves the date and merges the cells but the numbers are going like this

enter image description here

But I want it to be like this (merged into one cell and without the border.

enter image description here

What am I doing wrong in my code?

Upvotes: 0

Views: 44

Answers (1)

urdearboy
urdearboy

Reputation: 14580

I cant imagine why you would want to merge cells in such a way, but you were close none the less.

Since your range is static, define your range explicitly. Avoid .Selection & .Select when possible. 

Sub Test()

Dim selectedCells As Range
Dim cell As Range
Dim cellText As String

Application.DisplayAlerts = False
cellText = ""

Set selectedCells = Range("A1:H6")
For Each cell In selectedCells
    cellText = cellText & cell.Value & " "
Next

selectedCells.Merge
selectedCells.Value = Trim(cellText)
selectedCells.WrapText = True
Application.DisplayAlerts = True

End Sub

You can find lists of cell appearance properties online or here is the first one Google pulled for me. here

You can use the With feature to quickly apply a bunch of formats to your range without having to continuously qualify the range

With selectedcells
    .Merge
    .Value = Trim(cellText)
    .WrapText = True
End With

Upvotes: 1

Related Questions