Excel 2003 macro loop?

Question

I have 1000 lines of data from A1:D1000.

A= Names
B= Values
C= Values
D= Y or N

For example how do I write the code to do this:

"If cell "D5" equals "N", delete row; EXCEPT IF A5 = NAME XX"

and keeps looping to row 1000.

Answer 1

Hi,
write the following code:
'-------------------------------------------------------------
Public Sub s()
assuming that first row contains header
For i = 2 To 1000
If Sheet1.Cells(i, 1) <> "XX" And Sheet1.Cells(i, 4) = "n" Then Sheet1.Rows(i).Delete
Next i
End Sub
'--------------------------------------------------------------------------------
for safety reasons first u copy ur data in another sheet then use this code because this will delete rows then u cant undo it. u have to run this code more than once until u get desired results. since it deletes rows consequently row index changes so u have to run it more than once.
this code will affect data in sheet1 only.

Answer 2

I don't recommend iqbal's method. It may work for the first time you want to use it, but if you ever get over 1,000 names you will have to adjust your quantities. If I knew how many names were on your exception list I could tell you a little better, but I'll give you a really good example to use.

Sub MyRowDeleter

Dim TempRange1 As Range
Dim TempRange2 As Range
Dim Temp1Used As Range
Dim Temp1Col As Range
Dim Temp2Used As Range
Dim Temp2Col As Range
Dim MyCheck As Boolean

Application.ScreenUpdating = False

Set Temp1Used = Worksheets("Sheet1").UsedRange
Set Temp1Col = Worksheets("Sheet1") _
.Range("A:A")

Set TempRange1 = Intersect(Temp1Used, Temp1Col)

Set Temp2Used = Worksheets("Sheet2").UsedRange
Set Temp2Col = Worksheets("Sheet2") _
.Range("A:A")

Set TempRange2 = Intersect(Temp2Used, Temp2Col)

For Each x In TempRange1
If x.Offset(0, 3).Value = "N" Then
MyCheck = False
For Each y In TempRange2
If x.Text = y.Text Then
MyCheck = True
Exit For
End If
Next y
If MyCheck = False Then
x.EntireRow.Delete
End If
End If
Next x

End Sub


I know that my code is a lot longer and more complex but it will work whether you have 300 lines of names or 30,000. And it will run faster than iqbal's version due to the fact that the screen updating is turned off. You want to turn the screen updating off for most macros, especially ones of this type. If screen updating is left on (which by default it is) it updates the screen each time a change is made by the macro. Each screen update takes a little bit of time so the more changes you have the more it slows the macro down.

The way my code works is Sheet1 is the worksheet name of the worksheet tab that you would have your Columns A through D on and all of your data. On Sheet2 in Column A you would have an exception list. If the name is on the exception list the macro won't delete the row even if it has an N in the D column. My code also uses the intersection of the used range (the smallest block that contains all of the data that you've entered into the worksheet) and the column. So it returns a shortened version of the column so it doesn't keep comparing for the 65,000 some rows that Excel has. This also allows the macro the flexibility to adjust automatically based on how many entries you have.

If you are new to writing VBA coding or programming you may wonder why I used Set to set the values for the ranges instead of just using an equals sign. Ranges are considered objects, not a value or a string. And for objects you have to use Set.

As iqbal mentioned save the file under a different name or at the very least save the file before you run any macro. You can't just hit the undo button with a macro. If you at the very least save the file before you run the macro, if the macro messes up your file you can close the file before saving (most of the time) and revert to the way the file was before the macro was run. However it is safer to save the file under a different name then run the file. Because if the macro saves the file anywhere inside the macro code or you accidentally save the file before closing you won't be able to go back to the original.

Answer 3

Hi Lai

Your loop has to work from the end to the bigining of the range, not the otherway...

sub YourMacroName()
dim MyRange as range
set MyRange = range("a1:d1000") ' This can be a dynamic range (mutch better, i recomend it)

'this is the loop

For x = MyRange.Rows.Count To 1 Step -1
If ucase(MyRange.Cells(x, 4).Value) = "N" And MyRange.Cells(x, 1).Value <> "NAME XX" Then
MyRange.Rows(x).EntireRow.Delete
End If
Next

end sub

there is a much better and elegant way... but this is the basic

if you need more help contact me
[e-mail]
please vote!!!

Answer 4

It sounds like you have added this function in a Change Data type of event. So, once you have deleted the row, the data changes which recursive calls your function to once again delete the information.

Look for other event types for the macro.