Cooy only those rows which are filled in excel

Question

I need one help in the excel.I have a worksheet which contains 10 rows and 7 columns . If all columns are filled ,then I am putting "ok" in column 8 otherwise Data incomplete". It is working perfectly fine . But the problem is when I leave other column blank (B, C,D & E ) except column number A & B (they are filled). it is still printing "OK" but it should be "Data Incomplete" . Please help me how should I implement this logic here .

lastrow = sheet1.Range("A" & Rows.Count).End(xlUp).Row For rownum = 1 To lastrow

For colnum = 1 To 7

  If IsEmpty(Cells(rownum, colnum)) Then   '<== To Check for Empty Cells
    Cells(rownum, 8) = "Data Incomplete"     '<== To Enter value if empty cell found
    Cells(rownum, colnum).Interior.ColorIndex = 3
    MsgBox "Please fill the column highlighted by Red"
    
  Else
    Cells(rownum, 8) = "OK"
 End If
Next colnum

Next rownum

You can do all this with a formula and conditional formatting (minus the MsgBox of course). Is VBA a necessity? If so, WorksheetFunction.CountBlank, or WorksheetFunction.CountA, may be helpful here. In any case, there is no need to loop over every individual cell. — BigBen

TechnoDabbler TechnoDabbler · Accepted Answer · 2020-08-06T03:49:05

As BigBen says ... you can do this with formulas and conditional formatting. However if you do want a VBA solution ... rearrange your logic as follows. Note that it doesn't reset the fill color ... but you could add that if you wanted.

    Option Explicit
    
    
    Public Sub CheckEmptyCells()
    
        Dim lastrow As Long
        Dim rownum As Long
        Dim colnum As Long
        Dim EmptyCellFound As Boolean
        
        lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
        For rownum = 1 To lastrow
            EmptyCellFound = False
            For colnum = 1 To 7
                If IsEmpty(Cells(rownum, colnum)) Then   '<== To Check for Empty Cells
                  Cells(rownum, colnum).Interior.ColorIndex = 3
                  EmptyCellFound = True
                  MsgBox "Please fill the column highlighted by Red"
                End If
            Next colnum
                
            If EmptyCellFound Then
                Cells(rownum, 8) = "Data Incomplete"     '<== To Enter value if empty cell found
            Else
                Cells(rownum, 8) = "OK"
            End If
            
        Next rownum

End Sub

Cooy only those rows which are filled in excel

1 Answers