Fixing Run-Time Error 438

[coliervile]

I have a range of cells on Sheet1, (“C2:N35”), that have values/text in. Some cells are empty and I want to merge the blank cells to the cells above with values/text in the same column. I though that the following macro would work, macro is in Module1. When I run the macro I get the following error “Run-time error ‘438’: Object Doesn't Support This Property or Method". I don’t know where to fix the error. Where have I gone wrong??? I’m running Excel 2016.

Thanks,
Charlie

Option Explicit

Sub MergeCells()
Dim LRow As Long
Dim MyRng As Range
Dim c As Range
Dim MergRng As Range

Worksheets(“Sheet1”).Active
Range("C2").Activate
LRow = ActiveCell.CurrentRegion.Rows.Count
Set MyRng = Range("C2:N35”)

For Each c In MyRng
If c.Value = "" Then
Set MergRng = Range(c, c.Offset(-1, 0))
With MergRng.Cells
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
End If
Next c
End Sub

 

[shg]

Worksheets(“Sheet1”).[COLOR="#FF0000"]Select[/COLOR]

 

[coliervile]

Thank you “shg” for looking at my problem. I change Active to Select and I get the same error. When I debug it highlights “Range(C2”).Active

 

[shg]

You have open & closing quotes around Sheet1. VBA uses straight quotes.

 

[shg]

Replace both lines with

Application.GoTo Worksheets("Sheet1").Range("C2")

 

[coliervile]

“shg” I save the change you recommended and the macro ran, but it did run as I had hoped. Some cells merged, but they didn’t merge with the cells above with valid/text. When I run the macro I get the following Excel message “Merging cells only keeps the upper-left value and discards other values.” All the values remained on the sheet and when I debugged it it highlights “.MergeCells = True”

 

[coliervile]

Making the Application change gives a “Compile error: Expected Function or variable” and highlights “.GoTo”

 

[shg]

The universal solution to problems with merged cells is to not merge cells.

 

[coliervile]

It’s that bad huh?

 

[Mentor82]

Hi,
Try changing the following two lines accordingly. It may help.

If c.Offset(1,0).value <> "" Then
Set MergRng = Range(c, c.Offset(1, 0))