CODEWITHSUNDEEP
vbaexcel
charlespwd
charlespwd

Reputation: 538

Delete column without deleting merged cells - excel vba

How can I delete the left-most column of a merged cell without deleting its content?

For instance, range("A1:C1") is merged with .value="Hi"

By doing range("A2").EntireColumn.delete(xlLeft), I do not lose the merged cell (which now becomes range("A1:B1"), but I lose its content.

Upvotes: 0

Views: 3006

Answers (2)

charlespwd
charlespwd

Reputation: 538

I had to deal with merged cells addresses being unknown. Below is my solution for a variable range:

'' The Microsoft Scripting Runtime must be activated for this to work
'' Assumes rngDelete contains only one column
Sub DeleteColumnRetainMergedCellValues(rngDelete As Range)
Dim dicMerged As Dictionary
Set dicMerged = New Dictionary
Dim key As Variant
Dim rngCell As Range
Dim rngColumn As Range
Dim rngOptimizedColumn As Range

'' optimize the search range
Set rngColumn = rngDelete.EntireColumn
Set rngOptimizedColumn = Range( _
    rngColumn.Cells(1, 1), _
    rngColumn.Cells(rngColumn.Rows.Count, 1).End(xlUp))

'' find all merged cells, store their addresses and values
For Each rngCell In rngOptimizedColumn.Cells
    If rngCell.MergeArea.Cells.Count > 1 Then
        dicMerged.Add _
            rngCell.MergeArea.Cells(1, 1).Address, _
            rngCell.MergeArea.Cells(1, 1).Value
    End If
Next rngCell

'' delete the column
rngDelete.EntireColumn.Delete (xlLeft)

'' paste back the value in the merged cells
For Each key In dicMerged
    Range(key).Value = dicMerged(key)
Next key
End Sub

Upvotes: 1

neizan
neizan

Reputation: 2321

Why not just hold the value in a temp variable:

Sub DeleteColumnRetainMergedCellValue()
    'Select merged cells, then run this subroutine.
    Dim v As String
    v = Selection.Cells(1, 1).Value
    Selection.Cells(1, 1).EntireColumn.Delete
    Selection.Cells(1, 1).Value = v
End Sub

Is this what you're looking for? If not, please explain a little more.

Upvotes: 2

Related Questions