Grabbing data without duplicating the information VBA


Keywords:vba 


Question: 

I am creating a large collection of column titles for data underneath that are left blank.

Example of table

Col 1 | Col 2 | Col3 | Col 4 |
blank | info | blank| info  |

The code I'm using to collect these blanks and paste them in 'debug.print' to show the columns that require information.. the end result should be:
The information missing is :
Col1
Col3
It is alot larger than my example please can someone help on where i am going wrong as it duplicates the information when i loop it.

Do Until iCol = 34
    If Cells(iRow, iCol) = "" Then
        CellMissInfo = CellMissInfo & CurrTitleCol & ": " & vb_
        Debug.Print (CellMissInfo)
    End If
    iCol = iCol + 1
Loop

EDIT: Some more code to help

iRow = 3
iCol = 22
lastRow = Application.WorksheetFunction.CountA _
(Range("B2", Range("B" & Rows.Count).End(xlUp)))

Do While iRow <= lastRow

    If CurrPipeC <> RGB(242, 220, 219) Then 'Pipeline Color'
        Do Until iCol = 34
'HERE is my problem'
            If Cells(iRow, iCol) = "" Then
                CellMissInfo = CellMissInfo & CurrTitleCol & ": " & vb_
            End If
            iCol = iCol + 1
        Loop

NEW IDEA : Maybe if i create the columns as an array and then run through LBound to Ubound for column names adding the strings together?


1 Answer: 

The problem is that you are concatenating the strings as you loop over the columns and also printing the string each time.

As I see it, you have two options here: Either build up a string of all the missing data and print it at the end. Or print out each missing item as you find it

Option 1: Build up a string and print at the end:

Do Until iCol = 34
    If Cells(iRow, iCol) = "" Then
        CellMissInfo = CellMissInfo & CurrTitleCol & ": " & vb_
    End If
    iCol = iCol + 1
Loop
Debug.Print (CellMissInfo)

Option 2: Print each missing item as you find it

Do Until iCol = 34
    If Cells(iRow, iCol) = "" Then
        CellMissInfo = CurrTitleCol & ": " & vb_
        Debug.Print (CellMissInfo)
    End If
    iCol = iCol + 1
Loop