Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
Macro for deleting rows and serialising the remaing rows | Setting up and Configuration of Excel | |||
Macro for deleting rows and serialising the remaing rows | Excel Worksheet Functions | |||
Help!! I have problem deleting 2500 rows of filtered rows!!!! | Excel Discussion (Misc queries) | |||
deleting hidden rows so i can print only the rows showing?????? | Excel Worksheet Functions |