Cell merge failing

[Poor Dave]

Good morning all. My code below is bugging on Range(cell, cell.Offset(1, 0)).Merge on the very last row of my worksheet, and my belief is because the currency value in the "N" column that I'm merging on the last two cells is $0.00 with the very next cell being blank. I have other cells with $0.00 values that merge correctly but not these two cells. Doesn't bug if I temporarily change the cells value to $1.00.
Wondering what I might change to avoid this. (Have I said I friggin' hate merging.)
Thanks.

Dave

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim nextrow2 As Currency
nextrow2 = Range("N" & Rows.Count).End(xlUp).Row
Set nextRange2 = Range("N1:N" & nextrow2)
Check2:
For Each cell In nextRange2
If cell.Value = cell.Offset(1, 0).Value And cell.Value <> "" Then
Range(cell, cell.Offset(1, 0)).Merge
cell.VerticalAlignment = xlTop
GoTo Check2:
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True

 

[HaHoBe]

Hi Dave,

this works on my sample for the worst feature in Excel:

Public Sub MrE_1229362_1702711()
' https://www.mrexcel.com/board/threads/cell-merge-failing.1229362/
Dim lngStart As Long
Dim lngEnd As Long
Dim rngCell As Range
  
lngStart = 1

For Each rngCell In Range(Cells(lngStart, "N"), Range("N" & Rows.Count).End(xlUp))
  If rngCell.Value = rngCell.Offset(1, 0).Value And rngCell.Value <> "" Then
    lngEnd = rngCell.Row
  Else
    If lngEnd > lngStart Or rngCell.Row > lngStart Then
      With Range(Cells(lngStart, "N"), Cells(rngCell.Row, "N"))
        .Offset(1).Resize(.Rows.Count - 1).Value = vbNullString
        .Merge
        .VerticalAlignment = xlTop
      End With
    End If
    lngStart = rngCell.Row + 1
  End If
Next rngCell
End Sub

Ciao,
Holger

 

[Joe4]

Have I said I friggin' hate merging

You are not alone, you have a lot of company there.
Many believe that Merged Cells are just about the worst feature in Excel, and cause all sorts of issues with things like VBA, sorting, filtering, etc.
Most serious programmers tried to avoid them at all costs (and discourage the use of them at all costs).
Based on your question, I am guessing it is not by your choice that you are having to deal with them!

 

[Poor Dave]

Hi Dave,

this works on my sample for the worst feature in Excel:

Public Sub MrE_1229362_1702711()
' https://www.mrexcel.com/board/threads/cell-merge-failing.1229362/
Dim lngStart As Long
Dim lngEnd As Long
Dim rngCell As Range
 
lngStart = 1

For Each rngCell In Range(Cells(lngStart, "N"), Range("N" & Rows.Count).End(xlUp))
  If rngCell.Value = rngCell.Offset(1, 0).Value And rngCell.Value <> "" Then
    lngEnd = rngCell.Row
  Else
    If lngEnd > lngStart Or rngCell.Row > lngStart Then
      With Range(Cells(lngStart, "N"), Cells(rngCell.Row, "N"))
        .Offset(1).Resize(.Rows.Count - 1).Value = vbNullString
        .Merge
        .VerticalAlignment = xlTop
      End With
    End If
    lngStart = rngCell.Row + 1
  End If
Next rngCell
End Sub

Ciao,
Holger

Mr H.

Thank you. I appreciate your knowledge and help.

(Also wish I had an Excel code book to refer to so I could see the syntax with the arguments, etc. Might make my learning curve on some of these easier.)

Dave

 

[Joe4]

Please note, when marking a post as the solution, please mark the actual reply that has the solution (not your own post acknowledging that another post was the solution).
You should only mark you own post as the solution if you came up with the solution yourself, and have posted it here.
I have updated this for you.

 

[Poor Dave]

You are not alone, you have a lot of company there.
Many believe that Merged Cells are just about the worst feature in Excel, and cause all sorts of issues with things like VBA, sorting, filtering, etc.
Most serious programmers tried to avoid them at all costs (and discourage the use of them at all costs).
Based on your question, I am guessing it is not by your choice that you are having to deal with them!

Yeah, Not my choice. The data dump at night is rows of part numbers tied to the Material Request that issued them, tied to the Task Number the MR is issued to, and tied to the Work Order the task is written against. For every part number the data dump row duplicates the MR, WO, & Task number and makes their report nearly impossible to read. Just trying to make my life easier.

Dave

 

[Poor Dave]

Please note, when marking a post as the solution, please mark the actual reply that has the solution (not your own post acknowledging that another post was the solution).
You should only mark you own post as the solution if you came up with the solution yourself, and have posted it here.
I have updated this for you.

10-4. Thought I selected Mr H response correctly. Will look closer next time.

Dave

 

[Joe4]

Yeah, Not my choice. The data dump at night is rows of part numbers tied to the Material Request that issued them, tied to the Task Number the MR is issued to, and tied to the Work Order the task is written against. For every part number the data dump row duplicates the MR, WO, & Task number and makes their report nearly impossible to read. Just trying to make my life easier.

Dave

Yeah, I have had to deal with those headahces too. I have had to work with Reports that have page headers and footers. Most of the macro is cleaning up the data and getting rid of all the garbage in order to make it something I can work with first! If possible to remove the Merged Cells in your Macro, you may want to consider that, so it makes it easier to work with.