For...Each + Merging Cells Leading to Error 2042 (Type Mismatch)?

[mszekely]

I'm trying to cycle through a range of cells and merge all of the duplicates that are adjacent to one another (I know, merging cells is stupid but this is being required of me). It works for a few lines before returning an error. I'm not sure if it's the merged cells that's causing this. In my locals window, I have ACell = 0 when I first start to step in but it never changes to = 1 or = 2, it no longer returns an integer for me. Here's the code that's causing the issue (ACell is defined as range and FinalRow is defined as long):


Code:

Application.DisplayAlerts = False '<--- turns off the 'OK auto popup box'


For Each ACell In Range("d3:d" & FinalRow)
If ACell.Offset(1, 0).Value = "" Or ACell.Value = "" Then
' <---- if...then do nothing
Else
ACell.CurrentRegion.Columns(4).Merge
End If
Next


Application.displaylalerts = True


Any insights appreciated!

[Paul_Hossler]

Welcome to VBAExpress

If you use the [#] it will add CODE tags. Pasting your macro between will set it off and format it

I'd do something like this (assuming I'm understanding correctly)





Option Explicit

Sub Test()
Dim FinalRow As Long, i As Long

FinalRow = 17

Application.DisplayAlerts = False '<--- turns off the 'OK auto popup box'

With ActiveSheet.Columns(4)
For i = FinalRow - 1 To 3 Step -1
If .Cells(i).Value = .Cells(i - 1).Value Then
Range(.Cells(i - 1), .Cells(i)).Merge
End If
Next I
End With


Application.DisplayAlerts = True

End Sub