deleting rows

  • Thread starter Thread starter Andy Copeland
  • Start date Start date
A

Andy Copeland

Hi,
I am trying to delete all the rows where a zero value is in
column C. This is what I came up with but it deletes everything in the
column and none of the rows. I appreciate any help. I am using Excel 2000.
Thanks.

Sub DeleteRows()
If TypeName(Selection) <> "Range" Then Exit Sub
On Error Resume Next
For Each Cell In Selection
If Cell.Value = 0 Then Row.Select = True Else Row.Select False
If Selection = True Then Selection.Delete
Next Cell
End Sub
 
Hi Andy,
You also want to start from the bottom using STEP -1

However, possibly something like this would work for with no loops.

Columns("A:A").Replace 0, "", xlWhole
On Error Resume Next ' In case there are no blanks
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange 'Resets UsedRange for Excel 97


HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
Andy,

There is a big problem with this approach, if you have a zero in adjacent
rows, the second gets passed over. This is because the delete moves down to
the next row, but the Next command goes past it before it gets deleted.

An alternative approach is as follows

Sub DeleteRows()
Dim cell As Range
Dim i As Long
If TypeName(Selection) <> "Range" Then Exit Sub
On Error Resume Next
For i = Selection.Row + Selection.Rows.Count - 1 To Selection.Row
Step -1
If Cells(i, "C").Value = 0 Then
Cells(i, "C").EntireRow.Delete
End If
Next i
End Sub

which works bottom up, avoiding the problem.
 
I'm trying to delete the last row in a sheet, but the total number of
rows varies. It will always have the same word in the first column of
the last row.

How can I do this?

Jessi
 
I figured out the last problem I had and now I'm stuck again.

I want to compare data in column A with data in column E. If (row by
row) A is not equal to E, I want to delete A through D on that row.
I'm not even sure how to begin this.

Thanks,

Jessi
 
Back
Top