Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Delete Row
The following code does not seem to work in Excel 2000. It worked in Excel
XP. I need to evaluate a cell, starting at D2, and then delete the row if the cell is empty stopping at the last row. The column being evaluated contains dates so if the item is not closed the cell is empty. Dim TestColumn As Long Dim cRows As Long Dim i As Long TestColumn = 1 cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row Range("D2").Select For i = 2 To cRows If ActiveCell.Value 0 Then Selection.EntireRow.Delete Next i -- Sincerely, Tom Fortune |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Delete Row
"Tom Fortune" wrote in message ... The following code does not seem to work in Excel 2000. It worked in Excel XP. I need to evaluate a cell, starting at D2, and then delete the row if the cell is empty stopping at the last row. The column being evaluated contains dates so if the item is not closed the cell is empty. Dim TestColumn As Long Dim cRows As Long Dim i As Long TestColumn = 1 cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row Range("D2").Select For i = 2 To cRows If ActiveCell.Value 0 Then Selection.EntireRow.Delete Next i -- Sincerely, Tom Fortune Wher does your code fail? try somthing like On Error GoTo ErrorHandler Dim TestColumn As Long Dim cRows As Long Dim i As Long TestColumn = 1 cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row Range("D2").Select For i = 2 To cRows If ActiveCell.Value 0 Then Selection.EntireRow.Delete Next i exit Sub ErrorHandler MsgBox Err.Message /Fredrik |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Delete Row
It acutally looks like it completes all steps of the macro, but no lines are
deleted. I have tried stepping through the macro and it goes through the step but nothing happens and I do not get an error. It is as if the For/Next loop does not do anything! What would be the code to display the current value of cRows during the loop so I can see if anything is actually happening? Is the cRows= statement correct? Thanks "Fredrik Wahlgren" wrote: "Tom Fortune" wrote in message ... The following code does not seem to work in Excel 2000. It worked in Excel XP. I need to evaluate a cell, starting at D2, and then delete the row if the cell is empty stopping at the last row. The column being evaluated contains dates so if the item is not closed the cell is empty. Dim TestColumn As Long Dim cRows As Long Dim i As Long TestColumn = 1 cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row Range("D2").Select For i = 2 To cRows If ActiveCell.Value 0 Then Selection.EntireRow.Delete Next i -- Sincerely, Tom Fortune Wher does your code fail? try somthing like On Error GoTo ErrorHandler Dim TestColumn As Long Dim cRows As Long Dim i As Long TestColumn = 1 cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row Range("D2").Select For i = 2 To cRows If ActiveCell.Value 0 Then Selection.EntireRow.Delete Next i exit Sub ErrorHandler MsgBox Err.Message /Fredrik |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Delete Row
"Tom Fortune" wrote in message ... It acutally looks like it completes all steps of the macro, but no lines are deleted. I have tried stepping through the macro and it goes through the step but nothing happens and I do not get an error. It is as if the For/Next loop does not do anything! What would be the code to display the current value of cRows during the loop so I can see if anything is actually happening? Is the cRows= statement correct? Thanks "Fredrik Wahlgren" wrote: I haven't examined your code. If it's a Sub, use MsgBox. You can also step thru tehe code, the debugger should show the value of cRows. /Fredrik /Fredrik |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Delete Row
You are examining the same cell over-and-over.
You could put in an activecell.offset(1,0) to move to the next cell. "Fredrik Wahlgren" wrote in message ... "Tom Fortune" wrote in message ... It acutally looks like it completes all steps of the macro, but no lines are deleted. I have tried stepping through the macro and it goes through the step but nothing happens and I do not get an error. It is as if the For/Next loop does not do anything! What would be the code to display the current value of cRows during the loop so I can see if anything is actually happening? Is the cRows= statement correct? Thanks "Fredrik Wahlgren" wrote: I haven't examined your code. If it's a Sub, use MsgBox. You can also step thru tehe code, the debugger should show the value of cRows. /Fredrik /Fredrik |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Delete Row
Tom,
If I understand you correctly then you may consider the following code. Also, I very much doubt the code you show could work correctly in any version of Excel (unless I misundrestood the task) Regards, KL Sub DeleteRows() Dim cRows As Single Dim i As Single cRows = Cells(Rows.Count, 1).End(xlUp).Row For i = cRows To 2 Step -1 If Cells(i, 4) 0 Then Rows(i).Delete Next i End Sub "Tom Fortune" wrote in message ... The following code does not seem to work in Excel 2000. It worked in Excel XP. I need to evaluate a cell, starting at D2, and then delete the row if the cell is empty stopping at the last row. The column being evaluated contains dates so if the item is not closed the cell is empty. Dim TestColumn As Long Dim cRows As Long Dim i As Long TestColumn = 1 cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row Range("D2").Select For i = 2 To cRows If ActiveCell.Value 0 Then Selection.EntireRow.Delete Next i -- Sincerely, Tom Fortune |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Delete Row
Thanks all. I finally figured it out. For cRows =..., the column that it
was using to count contained no entries, so the count was zero!!!!! This code waorks because I sort prior to deleting so that all rows that need to be deleted are together. When it encounters the first row with a blank cell, it sits there until finishing the loop. I then have it resort by another column. "KL" wrote: Tom, If I understand you correctly then you may consider the following code. Also, I very much doubt the code you show could work correctly in any version of Excel (unless I misundrestood the task) Regards, KL Sub DeleteRows() Dim cRows As Single Dim i As Single cRows = Cells(Rows.Count, 1).End(xlUp).Row For i = cRows To 2 Step -1 If Cells(i, 4) 0 Then Rows(i).Delete Next i End Sub "Tom Fortune" wrote in message ... The following code does not seem to work in Excel 2000. It worked in Excel XP. I need to evaluate a cell, starting at D2, and then delete the row if the cell is empty stopping at the last row. The column being evaluated contains dates so if the item is not closed the cell is empty. Dim TestColumn As Long Dim cRows As Long Dim i As Long TestColumn = 1 cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row Range("D2").Select For i = 2 To cRows If ActiveCell.Value 0 Then Selection.EntireRow.Delete Next i -- Sincerely, Tom Fortune |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro warning - how to delete macro | Excel Worksheet Functions | |||
delete a macro that isn't in macro list | Excel Discussion (Misc queries) | |||
How can I delete a macro when the Delete button is not active? | Excel Worksheet Functions | |||
How do i delete a macro in Excel 2003 when delete isn't highlight | Excel Discussion (Misc queries) | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming |