Cell Deleting and Shifting

[Guest]

Dim lastrow As Long, i As Long
Dim cell As Range

lastrow = Cells(Rows.Count, "I").End(xlUp).Row
For i = lastrow To 1 Step -1
Set cell = Cells(i, "I")
If IsNumeric(cell) Then
If cell < 7 Then
cell.Offset(0, -4).Resize(1, 6).Delete
End If
End If
Next

This formula works great, THOUGH I want it to do something a little
different. It deletes the appropriate cells, however I want it to shift the
information in the cells to the right of the deleted cells over into their
place(so shift to left). Right now it deletes everything then shifts it up.
So where do I make the change? THANKS!!!

 

[Die_Another_Day]

lastrow = Cells(Rows.Count, "I").End(xlUp).Row
For i = lastrow To 1 Step -1
Set cell = Cells(i, "I")
If IsNumeric(cell) Then
If cell < 7 Then
cell.Offset(0, -4).Resize(1, 6).Delete shift:=xlToLeft
End If
End If
Next

HTH

Die_Another_Day

P.S. Have you ever tried using the macro recorder to figure these
things out?

 

[Guest]

That works great, NOW: I use this formula a couple times, when it gets to
the last row of data, I want it to do the deletion without shifting the rows
up, what do I have to put in to avoid that? Without the shift part, it
simply delets then shifts all remaining rows up, and I don't want that.
Thanks!!!!

 

[Die_Another_Day]

if all you are wanting to do is clear the data then use .ClearContents
instead of .Delete otherwise I don't quite understand what you are
wanting can you elaborate please?

HTH

Die_Another_Day