Concatenate skip rows with empty cells

Question

So my question is this I have this code here:

Private Sub Validate_Input_Click()
  Dim temp As String
  For Row = 2 To 250
    temp = ""
    For col = 2 To 12
      If Cells(Row, col) <> "" Then
        If temp <> "" Then temp = temp & "_"
        temp = temp & Cells(Row, col)
      End If
    Next col
    Cells(Row, 1) = temp
  Next Row
End Sub

I have information from Column 2 all the way to 12 and works perfect. But now I'm trying to figure out if lets say, Rows 2-6 have all information from lines 2-6 inputted correctly all cells are filled in, Line 7 is missing a cell or two that isn't filled out and lines 8-12 inputted correctly, all cells are filled in, how do I make it so the macro Concatenates lines 2-6, skips 7 because of blank cells, and continues to concatenate 8-12?

Also, not all rows from 2 - 250 are filled out, usually goes to about 60 to 75 depending on what I'm doing with the worksheet at that time. Just want the extra buffer just in-case also why I'm trying to figure out a way to skip over blank cells and not concatenate that specific row or rows that have blank cells.

I've been messing around with If Else statements but can't quite get it.

Any help would be great!

Looks syntactically correct; can you provide an example of what you're expecting to see and what you're actually seeing? — brianyates
@BYates So what I'm seeing is, if I have information from B - L, it will concatenate no matter what, which is what I want it to do, but if lets say, there is missing information on line 4, Column B & Column G, how do I go about making it so it does not concatenate line 4 and continues onto line 5 all the way down? — Maykid
So if there are any blanks in B through L, skip the entire row?? — Gary's Student
@Gary'sStudent Correct! if there are any blanks between B through L skip the entire row and continue onto the next row until it reaches the end 250 lines of the macro. — Maykid

Gary's Student Gary's Student · Accepted Answer · 2018-04-10T18:02:20

We test for any blanks before processing the row:

Sub Validate_Input_Click()
  Dim temp As String
  For Row = 2 To 250
    If Application.WorksheetFunction.CountBlank(Range(Cells(Row, 2), Cells(Row, 12))) = 0 Then
        temp = ""
        For col = 2 To 12
          If Cells(Row, col) <> "" Then
            If temp <> "" Then temp = temp & "_"
            temp = temp & Cells(Row, col)
          End If
        Next col
        Cells(Row, 1) = temp
    End If
  Next Row
End Sub

Concatenate skip rows with empty cells

1 Answers