deleting rows
 

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

deleting rows
 

Use

If cell.Value = 0 Then cell.EntireRow.Delete

regards,

Don Lloyd
--

"Andy Copeland" wrote in message
...
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

deleting rows
 

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).Enti reRow.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

"Donald Lloyd" wrote in message ...
Use

If cell.Value = 0 Then cell.EntireRow.Delete

regards,

Don Lloyd
--

"Andy Copeland" wrote in message
...
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

deleting rows
 

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.


--

HTH

Bob Phillips

"Andy Copeland" wrote in message
...
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

deleting rows
 

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

deleting rows
 

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