Do Not Copy Row(s) Based On Empty Cell

Question

I have a code that takes cells (column E) with a certain value (TI002768E2XA E005) from Sheet 1 and moves those selected cells (in columns B-F and variable rows) to Sheet 2. These cells will sometimes have blank values (usually columns D and F) which carry over to Sheet 2, but I don't want the rows that have blank cells. How do I keep from moving those rows with the blank cells from Sheet 1 to Sheet 2? Thanks for any help!

Sub SAPDashboard()

Dim LastRow As Long
Dim myRow As Long
Dim myCopyRow As Long
Dim LastRow1 As Long
      
myCopyRow = 3

LastRow = Sheets("Sheet1").Cells(Rows.Count, "E").End(xlUp).Row

Application.ScreenUpdating = False

For myRow = 1 To LastRow

    If Sheets("Sheet1").Cells(myRow, "E") = "TI002768E2XA E005" Then
        Sheets("Sheet2").Cells(myCopyRow, "B") = Sheets("Sheet1").Cells(myRow, "e")
        Sheets("Sheet2").Cells(myCopyRow, "C") = Sheets("Sheet1").Cells(myRow, "d")
        Sheets("Sheet2").Cells(myCopyRow, "D") = Sheets("Sheet1").Cells(myRow, "F")
        Sheets("Sheet2").Cells(myCopyRow, "E") = Sheets("Sheet1").Cells(myRow, "G")
        Sheets("Sheet2").Cells(myCopyRow, "F") = Sheets("Sheet1").Cells(myRow, "H")

        myCopyRow = myCopyRow + 1
    
    End If
    
Next myRow

LastRow1 = Range("b5000").End(xlUp).Row
LastRow = Range("A5000").End(xlUp).Row
Range("E" & LastRow1 + 1).Formula = "=SUM(E3:E" & LastRow1 & ")"
Range("F" & LastRow1 + 1).Formula = "=SUM(F3:F" & LastRow1 & ")"

Columns("A:AB").HorizontalAlignment = xlCenter
Range("B2:F2").Value = Array("Charge Code", "Name", "Title", "Cost", "Hours")
Worksheets("Sheet2").Range("B2:F2").Font.Bold = True
Range("B2:F2").Interior.ColorIndex = 15
Range("B2:F2").EntireColumn.AutoFit
Range("E:E").Style = "Currency"

         
Set rng = Nothing
      
With Range("B2:F" & Range("B" & Rows.Count).End(xlUp).Row)
  .Borders.ColorIndex = 1
  .Borders.Weight = xlThin
  .BorderAround , xlThick, 1
  .Resize(1).Borders(xlEdgeBottom).Weight = xlThick

End With

End Sub

Answer 1

Use the COUNTA function to check for blank cells in the source table

Dim srcRange As Range
For myRow = 1 To LastRow
    If Sheets("Sheet1").Cells(myRow, "E") = "TI002768E2XA E005" Then
        Set srcRange = Sheets("Sheet1").Cells(myRow, "d").Resize(1, 5)
        If NoEmpty(srcRange) Then
            Sheets("Sheet2").Cells(myCopyRow, "B") = Sheets("Sheet1").Cells(myRow, "e")
            Sheets("Sheet2").Cells(myCopyRow, "C") = Sheets("Sheet1").Cells(myRow, "d")
            Sheets("Sheet2").Cells(myCopyRow, "D") = Sheets("Sheet1").Cells(myRow, "F")
            Sheets("Sheet2").Cells(myCopyRow, "E") = Sheets("Sheet1").Cells(myRow, "G")
            Sheets("Sheet2").Cells(myCopyRow, "F") = Sheets("Sheet1").Cells(myRow, "H")
            myCopyRow = myCopyRow + 1
        End If
    End If
Next myRow



' UDF to validate the range
Function NoEmpty(dataRange As Range) As Boolean
    Dim c As Range
    For Each c In dataRange
        If Len(Trim(c.Value)) = 0 Then
            NoEmpty = False
            Exit Function
        End If
    Next
    NoEmpty = True
End Function