Delete all blank rows... need further help

ok I have this ....

Code:

---

LastRow1 = [A60000].End(xlUp).Row
For i = 1 To LastRow1
If Cells(i, 1) = "" Then
    With Rows(i & ":" & i).EntireRow
        .ClearContents
        .FormatConditions.Delete
    End With
   
End If
Next i

---

works great! and fast! but.. im just wondering how could this code be changed to do say "all" 17 columns?? in the same time.. Im doing this for blank spaces but for this example I call the blank space a * below.. so assume I want all the rows with *'s gone in a single instance..

eg
1|* |text|lala|1|26|text|lala|
1|26|text|lala|1|26|text|lala|
1|26|text| * |1|26|text|lala|
1|26|text|lala|1|26|text| * |
1|26|text|lala|1| * |text|lala|

right ?? so say I ONLY want completely filled rows and any incomplete dumped.

lemme know if this is possible.. or if I have to do 17 individual codes like above
changing all the i's to a b c d etc etc...

im thinking....

Code:

---

LastRow10 = [A60000].End(xlUp).Row
For i = 1 To LastRow1
If Cells(i, 1) = "" Then    '--------------here?? needs like (i, 1 -17) ??? dunno how to code it tho.
    With Rows(i & ":" & i).EntireRow
        .ClearContents
        .FormatConditions.Delete
    End With
   
End If
Next i

---

In order to remove all lines with empty cell in one action you need to address them as a range (or even as a region, which is a bundle of ranges that are not connected).
In other words you need to make all the steps to check wether each line has an empty cell, if yes add the line to the region/range and finally delete that region/range.
However, if you save the "add the line to the region/range" and use the delete on each line you will save one theoreticla action!

If you only want to clear the contents of the rows you can do it like this:

vb.net Code:

LastRow1 = [A60000].End(xlUp).Row
For i = 1 To LastRow1
    If WorksheetFunction.CountIf(Range(Cells(i, 1), Cells(i, 17)), "") > 0 Then
        With Rows(i).EntireRow
            .ClearContents
            .FormatConditions.Delete
        End With
    End If
Next i

But if you want to delete the whole row:

vb.net Code:

LastRow1 = [A60000].End(xlUp).Row
For i = LastRow1 To 1 Step -1
    If WorksheetFunction.CountIf(Range(Cells(i, 1), Cells(i, 17)), "") > 0 Then
        Rows(i).EntireRow.Delete
    End If
Next i

I do it backwards because if you go from 1 to LastRow you can miss some because each time you delete one the order changes. This does not apply if you only empty the contents of the row.

Here's another idea, I am still working on it because when the Find method does not find the blank space it throws an error, that is why the error handler is there.

vb.net Code:

lastRow = [A60000].End(xlUp).Row
On Error GoTo 1
   Do While Range("A1", Cells(lastRow, 5)).Find("").Activate
        
        ActiveCell.EntireRow.Delete
        lastRow = lastRow - 1
    Loop
1

You the man!! thanks! this one works flawless.. actually even better then the original one I was using.. cause this one can do 1 to.. who knows how many columns! awsome

Code:

---

LastRow1 = [A60000].End(xlUp).Row
For i = LastRow1 To 1 Step -1
    If WorksheetFunction.CountIf(Range(Cells(i, 1), Cells(i, 17)), "") > 0 Then
        Rows(i).EntireRow.Delete
    End If
Next i

---