Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code problem
I would like to delete rows in column c for a criteria with a macro code. But
I have a problem with my code, it doesn't work. Could anyone tell me what's wrong with my macro code? Thanks! Sub filter() Dim i As integer i=0 Do until i="" 'my criteria is that all rows that contain a value between 800 and 900 have to be deleted If Cells (i,3).Value=800 AND Cells(i,3)<=900 Then EntireRow.delete i=i+1 Loop End sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code problem
You have i as an integer (a number), but your loop says to do until i = empty
string. That can't happen. If you want the loop to run until you find an empty cell in column C, try it this way Do Until IsEmpty(Cells(i,3)) also, just for consistency, I'd change the IF statement to either IF Cells(i,3).Value =800 AND Cells(i,3).Value <=900 Then Cells(i,3).EntireRow.Delete End If or IF Cells(i,3) =800 AND Cells(i,3)<=900 Then Cells(i,3).EntireRow.Delete End If "Ruben" wrote: I would like to delete rows in column c for a criteria with a macro code. But I have a problem with my code, it doesn't work. Could anyone tell me what's wrong with my macro code? Thanks! Sub filter() Dim i As integer i=0 Do until i="" 'my criteria is that all rows that contain a value between 800 and 900 have to be deleted If Cells (i,3).Value=800 AND Cells(i,3)<=900 Then EntireRow.delete i=i+1 Loop End sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code problem
Ruben,
One problem in deleting rows and using a counter/row pointer such as you have done is that when a row is deleted, you end up skipping rows that may have values in them that you want to delete. One easy way to do this is to start at the bottom of the list and work your way up it instead of from the top down. Try this code Sub DeleteSomeRows() Dim lastRow As Long Dim rowPointer As Long 'find last used row in column C lastRow = Cells(Rows.Count,3).End(xlUp).Row 'work from bottom up For rowPointer = lastRow To 1 Step -1 If Cells(rowPointer,3)=800 And _ Cells(rowPointer,3)<=900 Then Cells(rowPointer,3).EntireRow.Delete Next End Sub "Ruben" wrote: I would like to delete rows in column c for a criteria with a macro code. But I have a problem with my code, it doesn't work. Could anyone tell me what's wrong with my macro code? Thanks! Sub filter() Dim i As integer i=0 Do until i="" 'my criteria is that all rows that contain a value between 800 and 900 have to be deleted If Cells (i,3).Value=800 AND Cells(i,3)<=900 Then EntireRow.delete i=i+1 Loop End sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code problem
Assumes a header row
for i= cells(rows.count,3).end(xlup).row to 2 step -1 If Cells (i,3)=800 AND Cells(i,3)<=900 Then rows(i).delete next i -- Don Guillett Microsoft MVP Excel SalesAid Software "Ruben" wrote in message ... I would like to delete rows in column c for a criteria with a macro code. But I have a problem with my code, it doesn't work. Could anyone tell me what's wrong with my macro code? Thanks! Sub filter() Dim i As integer i=0 Do until i="" 'my criteria is that all rows that contain a value between 800 and 900 have to be deleted If Cells (i,3).Value=800 AND Cells(i,3)<=900 Then EntireRow.delete i=i+1 Loop End sub |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code problem
It works, thanks for helping!
Ruben "Don Guillett" wrote: Assumes a header row for i= cells(rows.count,3).end(xlup).row to 2 step -1 If Cells (i,3)=800 AND Cells(i,3)<=900 Then rows(i).delete next i -- Don Guillett Microsoft MVP Excel SalesAid Software "Ruben" wrote in message ... I would like to delete rows in column c for a criteria with a macro code. But I have a problem with my code, it doesn't work. Could anyone tell me what's wrong with my macro code? Thanks! Sub filter() Dim i As integer i=0 Do until i="" 'my criteria is that all rows that contain a value between 800 and 900 have to be deleted If Cells (i,3).Value=800 AND Cells(i,3)<=900 Then EntireRow.delete i=i+1 Loop End sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with code | Excel Discussion (Misc queries) | |||
CODE PROBLEM | Excel Discussion (Misc queries) | |||
VB Code Problem | Excel Discussion (Misc queries) | |||
XLS to CSV Code Problem | Excel Worksheet Functions | |||
Little problem with this code... | Excel Discussion (Misc queries) |