Trying to merge cells across with * at the start of the text

[Darren Smith]

Find the * in a text string then merge the text across the columns.
At the moment it seems to just merge a load of random cells

Sub MergeCells()
    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim myCriteriaColumn As Long
    Dim myFirstColumn As Long
    Dim myLastColumn As Long
    Dim ws As Worksheet
    Dim myCriteria As String
    Dim iCounter As Long
 
    myFirstRow = 13
    myCriteriaColumn = 5
    myFirstColumn = 5
    myLastColumn = 14
    myCriteria = "Merge cells"
 
    Set ws = ThisWorkbook.Worksheets("Job Card with Time Analysis")
 
    With ws
 
        myLastRow = .Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 
        For iCounter = myLastRow To myFirstRow Step -1
            If .Cells(iCounter, myCriteriaColumn).Value = myCriteria Then .Range(.Cells(iCounter, myFirstColumn), .Cells(iCounter, myLastColumn)).Merge
        Next iCounter
 
    End With
 

End Sub

 

[jasonb75]

Are the cells to the right completely empty?

Nullstrings from imported data, formulas that show "", etc. would mean that the cells are not empty so it will not spill over then.

Other than such content in the cells, I know of no reason why it would not work as normal.

 

[Darren Smith]

Data is from Access Database so properly are null. Can you override this?

 

[jasonb75]

Only by deleting the nullstrings from those cells.

Assuming that there is no actual data anywhere to the right of column E

ThisWorkbook.Worksheets("Job Card with Time Analysis").Range("F:N").ClearContents

 

[Darren Smith]

There is data to the right of the E column in rows without a * in them.

 

[jasonb75]

Give this a go on a copy of your file

Sub test
Dim c As Range, lRow As Long
With Worksheets("Job Card with Time Analysis")
    lRow = .Cells(Rows.Count,5).End(xlUp).Row
    For Each c In .Range("E13:E" & lRow)
        If Left(c.Value,1) ="*" Then c.Offset(,1).Resize(,9).ClearContents
    Next
End With
End Sub

 

[Darren Smith]

Sorry doesn't seem to work

 

[jasonb75]

Try selecting some of the 'empty' cells in a row with * then hit the delete key to clear them and see if the text flows across.

I've tested the code above and it works fine for me, if it is not working for you then there is a problem with your sheet that needs to be identified.

 

[Darren Smith]

Try selecting some of the 'empty' cells in a row with * then hit the delete key to clear them and see if the text flows across. I have done that earlier it didn`t work?

I've tested the code above and it works fine for me, if it is not working for you then there is a problem with your sheet that needs to be identified.

 

[jasonb75]

As long as the cell(s) immediately to the right are empty then the text will spill into them. If selecting them and clearing the contents is not allowing the text to spill then it would appear that something is wrong with your excel installation. I have made multiple attempts to recreate the problem without success, and have been through excel settings carefully and can find nothing that would cause the issue.

Without access to the workbook, I don't see that there is any way to identify the cause of the problem.

 

[Darren Smith]

Morning Jason75

Could I let you access my workbook?
Happy to send it to you via Dropbox on this forum.