deleting blank rows

[mcorydon]

Hello Guru's, I have a massive 1000+ row table in excel (A1:F1500) and I want to delete all of the blank rows. I know the F5, special, blanks trick but the problem is that some cells are blank while other cells in the same row have data. If I did the above trick it would eliminate E427 and A51 for example and that would mismatch the data (I don't want E428 being pushed into the E427 column) Any ideas? hope this made sense Thanks Y'all

 

[bill0145]

This will Remove the entire row if any cells are blank:

Sub Ifsomeareblank()
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1
    If Cells(i, 1) = "" Or Cells(i, 2) = "" Or Cells(i, 3) = "" Or Cells(i, 4) = "" Or Cells(i, 5) = "" _
    Or Cells(i, 6) = "" Or Cells(i, 7) = "" Or Cells(i, 8) = "" Or Cells(i, 9) = "" Or Cells(i, 10) = "" Then
    Range("A" & i).EntireRow.Delete shift:=xlUp
    End If
Next i
End Sub

This will only remove rows that are completely blank

Sub Ifallareblank()
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1
    If Cells(i, 1) = "" And Cells(i, 2) = "" And Cells(i, 3) = "" And Cells(i, 4) = "" And Cells(i, 5) = "" _
    And Cells(i, 6) = "" And Cells(i, 7) = "" And Cells(i, 8) = "" And Cells(i, 9) = "" And Cells(i, 10) = "" Then
    Range("A" & i).EntireRow.Delete shift:=xlUp
    End If
Next i
End Sub

 

[bill0145]

Sorry I forgot to change the number of columns for you:

Sub Ifsomeareblank()
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1
    If Cells(i, 1) = "" Or Cells(i, 2) = "" Or Cells(i, 3) = "" Or Cells(i, 4) = "" Or Cells(i, 5) = "" _
    Or Cells(i, 6) = "" Then
    Range("A" & i).EntireRow.Delete shift:=xlUp
    End If
Next i
End Sub

and:

Sub Ifallareblank()
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1
    If Cells(i, 1) = "" And Cells(i, 2) = "" And Cells(i, 3) = "" And Cells(i, 4) = "" And Cells(i, 5) = "" _
    And Cells(i, 6) = "" Then
    Range("A" & i).EntireRow.Delete shift:=xlUp
    End If
Next i
End Sub