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]

There is nothing in your code that will cause it to fail as long as it is being used correctly.

Are you trying to merge cells that start with "*" or those that contain "Merge cells"? Those are 2 different scenarios, a partial match will require the use of InStr while an exact match of entire contents will not.

Also, If in vba is case sensitive, so "Merge cells" and "merge cells" are not the same.

 

[Darren Smith]

There is nothing in your code that will cause it to fail as long as it is being used correctly.

Are you trying to merge cells that start with "*" or those that contain "Merge cells"? Those are 2 different scenarios, a partial match will require the use of InStr while an exact match of entire contents will not.

Also, If in vba is case sensitive, so "Merge cells" and "merge cells" are not the same.

Are you trying to merge cells that start with "*" Yes

 

[jasonb75]

In that case the variable, myCriteria should be "*", not "Merge cells". Also, as mentioned above, you need to use InStr for a partial match.

Note that I've used InStr(...) =1 in order to check that the * is the first character, without =1 it would return true for a * anywhere in the cell.

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 = "*"
 
    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 InStr(.Cells(iCounter, myCriteriaColumn).Value, myCriteria) = 1 Then .Range(.Cells(iCounter, myFirstColumn), .Cells(iCounter, myLastColumn)).Merge
        Next iCounter
 
    End With
 

End Sub

 

[Darren Smith]

Thank you very much it`s working fine now

 

[Darren Smith]

Can you merge it to the length of the text string

 

[jasonb75]

It might be possible but it is not something that I would attempt to do personally. Merged cells are a bad idea at the best of times, trying to do something like that is going to be messy and unnecessarily complicated.

 

[Darren Smith]

What about making the text visible without merging the cells is that possible?

 

[jasonb75]

As long as the cells to the right are empty the text (not numeric data) should be visible anyway, by default it will spill across empty cells.

 

[Darren Smith]

For some reason, it`s not working that way for me.