locked
VBA: if specific text found, merge cells RRS feed

  • Question

  • Hi Excel experts,

    I'm trying to merge cells if a cell has no text after a specific position, and i'm thinking VBA might be the best way to achieve it?

    So, basically, as in the example below, if there is no text after "00:05" (which is the 12th position) in a cell, merge the cell with a cell above it, and keep the value of the above cell. And apply this formatting to the used range of the whole worksheet. 

    Possible? Hope someone could help me with this...

    From

    To

    Thank you

    Jay

    Wednesday, November 7, 2018 9:35 AM

Answers

  • Hi jay,

    I assume that the columns of the cells that you want to merge are column B and column C.

    Please refer to the following code:

    Sub test()
    
      Dim i As Integer
    
      Dim row As Integer
    
      row = Range("B" & Rows.Count).End(xlUp).row()
    
      Application.DisplayAlerts = False
    
      For i = 2 To row
    
        If Len(Range("B" & i).value) = 11 Then
    
          Range("B" & (i - 1) & ":" & "B" & i).Merge
    
        End If
    
        If Len(Range("C" & i).value) = 11 Then
    
          Range("C" & (i - 1) & ":" & "C" & i).Merge
    
        End If
    
      Next i
    
      Application.DisplayAlerts = True
    
    End Sub


    Hopefully it helps you.

    Best Regards,

    Bruce


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    • Marked as answer by jay.nz Friday, November 9, 2018 9:58 AM
    Thursday, November 8, 2018 6:24 AM

All replies

  • Hi jay,

    I assume that the columns of the cells that you want to merge are column B and column C.

    Please refer to the following code:

    Sub test()
    
      Dim i As Integer
    
      Dim row As Integer
    
      row = Range("B" & Rows.Count).End(xlUp).row()
    
      Application.DisplayAlerts = False
    
      For i = 2 To row
    
        If Len(Range("B" & i).value) = 11 Then
    
          Range("B" & (i - 1) & ":" & "B" & i).Merge
    
        End If
    
        If Len(Range("C" & i).value) = 11 Then
    
          Range("C" & (i - 1) & ":" & "C" & i).Merge
    
        End If
    
      Next i
    
      Application.DisplayAlerts = True
    
    End Sub


    Hopefully it helps you.

    Best Regards,

    Bruce


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    • Marked as answer by jay.nz Friday, November 9, 2018 9:58 AM
    Thursday, November 8, 2018 6:24 AM
  • Thank you very much, Bruce. It works!

    To help me better understand it, can i ask a few questions as i'm a vba beginner?

    1. What is ":" for? 
    e.g. as in Range("B" & (i - 1) & ":" & "B" & i).Merge

    2. If i want to apply this rule to the whole used range, not just column B and C, what should i do?

    3. Is it necessary to Dim row? Can I not write like this?
    "For i = 2 to Range("B" & Rows.Count).End(xlUp).row()", instead of 
    " For i = 2 To row"


    • Edited by jay.nz Thursday, November 8, 2018 9:58 AM
    Thursday, November 8, 2018 9:56 AM
  • Hi jay,

    << 1. What is ":" for? 
          e.g. as in Range("B" & (i - 1) & ":" & "B" & i).Merge

    This ":" is equivalent to the ":" in Range("B1:B2"), used to select a range.

    << 2. If i want to apply this rule to the whole used range, not just column B and C, what should i do?

    You need to process each column. For example, there are D and E columns, you need to add the following code to the for loop:

    If Len(Range("D" & i).value) = 11 Then
    
      Range("D" & (i - 1) & ":" & "D" & i).Merge
    
    End If
    
    If Len(Range("E" & i).value) = 11 Then
    
      Range("E" & (i - 1) & ":" & "E" & i).Merge
    
    End If


    << 3. Is it necessary to Dim row? Can I not write like this?
    "For i = 2 to Range("B" & Rows.Count).End(xlUp).row()", instead of 
    " For i = 2 To row"

    You can write like this. It isn’t necessary to Dim row. 


    Best Regards,

    Bruce

        

    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    Friday, November 9, 2018 2:49 AM
  • Thank you so very much, Bruce!
    Friday, November 9, 2018 9:59 AM