Merge Cells in a Column

[Poor Dave]

Good afternoon all. Having trouble making this merge of cells work. Need to limit my merge to a specific column and my data in the column changes daily. The macro is merging the cell values as they repeat, but it is bugging out after all the merges are done on If cell.Value = cell.Offset(1, 0).Value And cell.Value <> “” Then. Is it due to the Range("K:K") code going to blank cells and it should be limited to the end of the column data? Or something else. I thought the And cell.Value <> “” Then would satisfy that. I appreciate your help.


Sub test()

'Merge similar cells

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set myRange = Range("K:K")
Check:
For Each cell In myRange
If cell.Value = cell.Offset(1, 0).Value And cell.Value <> “” Then
Range(cell, cell.Offset(1, 0)).Merge
cell.VerticalAlignment = xlCenter
GoTo Check
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[Z51]

You can get the last row in column K, e.g.

Dim lastrow as Long
lastrow = Range("K" & Rows.Count).End(xlUp).Row

Set myRange = Range("K1:K" & lastrow)

 

[Poor Dave]

Dim lastrow as Long lastrow = Range("K" & Rows.Count).End(xlUp).Row Set myRange = Range("K1:K" & lastrow)

Thanks 'Z'. Works as I need.

Dave

 

[Poor Dave]

Can mu

You can get the last row in column K, e.g.

Dim lastrow as Long
lastrow = Range("K" & Rows.Count).End(xlUp).Row

Set myRange = Range("K1:K" & lastrow)

Can multiple columns be 'ranged' at once to merge say column "S", "Z", etc. without rewriting this whole code set one after the other and just changing the column letter.
Thank you.

 

[Z51]

If you want all the ranges to stop at the same 'lastrow', sure.

Set myRng = Range("K1:K" & lastrow & ", S1:S" & lastrow & ", Z1:Z" & lastrow)

If you wanted to have different sized ranges (the last row in each column is different) you'd just need to determine the last row in each and perhaps assign each to its own variable, e.g. lastrowK, lastrowS and lastrowZ, then use those in your Set myRng statement where appropriate.

 

[Poor Dave]

If you want all the ranges to stop at the same 'lastrow', sure.

Set myRng = Range("K1:K" & lastrow & ", S1:S" & lastrow & ", Z1:Z" & lastrow)

If you wanted to have different sized ranges (the last row in each column is different) you'd just need to determine the last row in each and perhaps assign each to its own variable, e.g. lastrowK, lastrowS and lastrowZ, then use those in your Set myRng statement where appropriate.

Thanks Z. Thought it could be done but wasn't sure about the string language.

Dave

 

[Michael M]

@Poor Dave
Merging cells can create a lot of issues if you are using number data or need to further use VBA to do other tasks.
Without seeing your data, it would be wise to avoid merged cells whenever possible. Merged cells are the nightmare of VBA and Excel, should never have been a feature in Excel

 

[Poor Dave]

@Poor Dave
Merging cells can create a lot of issues if you are using number data or need to further use VBA to do other tasks.
Without seeing your data, it would be wise to avoid merged cells whenever possible. Merged cells are the nightmare of VBA and Excel, should never have been a feature in Excel

Thanks Mr Mike. I had read that in several of these messages to avoid where possible. Thankfully in my code I am merging after all other code is run so the printout doesn't have repeated data in the column.

Dave

 

[Michael M]

Ok....so long as you are aware of the possible issues...