delete rows dependent upon criteria within range

[djpeter]

The following code works, (does what its supposed to do) But not to the cells i want it too. basically, it won't delete all the way down to a40, i assume something with the loop is making it think it has done enough but it hasn't. When i run the code, it deletes any cells down to about a 25, is there a better way for this delete macro to be?? Thanks

Sheets("Investment Allocation").Select
Dim cell As Range
Range("A7:A40").Select
For Each cell In Selection
If cell = Empty Then
cell.EntireRow.Delete
End If
Next cell

 

[Bill_Biggs]

Try this:
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro1()<br>Sheets("Investment Allocation").Select<br><SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> J <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>J = 40<br><br><SPAN style="color:#00007F">For</SPAN> i = 7 <SPAN style="color:#00007F">To</SPAN> 40<br><br><br>Range(Cells(7, 1), Cells(J, 1)).Select<br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Selection<br><SPAN style="color:#00007F">If</SPAN> cell = <SPAN style="color:#00007F">Empty</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>cell.EntireRow.Select<br>Selection.Delete<br><br>J = J - 1<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN> cell<br><br><SPAN style="color:#00007F">Next</SPAN> i<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

You may want to add Application.Screenupdating=False.

HTH.

 

[Ragnar1211]

nvm

 

[VoG]

Try

Sub DelBlk()
With Sheets("Investment Allocation")
    On Error Resume Next
    .Range("A7:A40").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0
End With
End Sub

 

[Bill_Biggs]

Gerry, what is nvm? That was smooth, as always, Peter.

 

[Ragnar1211]

Sorry... got a phone call, didn't have time to write my response! It appears to have been handled now.