how to add a for loop to my code...

[thexfactor44]

I have code that compares a value on two sheets and if they are redundant, deletes the entire row on one sheet. I need to be able to incriment through all the cells in column A on Sheet 1 though, not like it is now, hard-coded to A1.
logically, i need this:
if "sheet2:cell An" (where 'n' is any cell in sheet 2:column A) is equal to "sheet1:cell An", then delete row on sheet1 where value is redundant.

Do i need a for loop? I have no idea. this is the current code i have come up with:

Sub X()

Sheets("Sheet2").Select
sheet2 = Range("A1")
Sheets("Sheet1").Select
sheet1 = Range("A1")
If sheet2 = sheet1 Then
Rows("1:1").Select
Selection.Delete Shift:=x1up
End If

End Sub

Thanks to all that can help!

[kkknie]

Here you go...

Sub DeleteRedundants()



Dim i As Integer, j As Integer



'Loop through first sheet

For i = 1 To 10000



   'Exit if blank cell found

   If Sheets("Sheet1").Cells(i, 1).Value = "" Then Exit For

   

   'Loop through second sheet

   For j = 1 To 1000

      'Exit if blank cell found

      If Sheets("Sheet2").Cells(j, 1).Value = "" Then Exit For

      'If duplicate found, delete the row and exit

      If Sheets("Sheet1").Cells(i, 1).Value = Sheets("Sheet2").Cells(j, 1).Value Then

         Sheets("Sheet2").Cells(j, 1).EntireRow.Delete

         Exit For

      End If



   Next

   

Next



End Sub

Hope this helps,

K

[ This Message was edited by: kkknie on 2002-05-24 08:58 ]

[thexfactor44]

thanks for the code. it works great. I have one issue though (due to my lack of explanation):

the two columns being compared are about 25000 cells deep. I realize I need to change your code on the range section to accomidate. The problem is that this method is too hard on memory and excel locks up prtty bad...for about 30 minutes. is there a way to go about this using less memory?

If there is another method and you exit on blank cells, couldn't the range (insidde the for;to) be set from 1 to 65000? thanks.

I appreciate your help.

[Joe Was]

I posted this code today for another, you may be able to exchange your test for the one in the code below. This code is fast!

This "Sheet Module" code will work from a hot-key or Form Button. For any value in Column "C" that is value less than 10 (<10) the entire row is deleted. Hope this helps. JSW

Sub DRow()
'Find all the rows for which column "C" < 10 and delete it.

Application.ScreenUpdating = False
Worksheets("Sheet1").Select
Range("C1").Select
n = 1
n = n + 1
For Each r In Worksheets("Sheet1").UsedRange.Rows
n = r.Row
If Worksheets("Sheet1").Cells(n, 3) < 10 Then
Worksheets("Sheet1").Cells(n, 3).Select
Selection.EntireRow.Delete
End If
Next r
Application.ScreenUpdating = True
'Range("A1").Select
End Sub


[ This Message was edited by: Joe Was on 2002-05-24 10:23 ]

[thexfactor44]

I wish I knew how to port the code to comething I could use, but I dont know VB at all..I am just playing with the methods I understand from scripting languages like Perl. It looks like you have the method right though, by switching variables into memory 1 at a time..this is what I would like to do, but i really dont know how. If this is easy, I would really appreciate help. If it is not, I will plug away and not waste all your time and try myself..as I would like to learn this stuff...just not when deadline is pushing. Thanks for your help though.

[thexfactor44]

I cannot do it from what i know. this is sorta a final cry for help...

[Joe Was]

Try this. Once the row addresses change due to a delete this code will ignore any duplicates who's row address does not match!

So after the first pass your two sheets will not match. Any duplicates not on the same row on both sheet will not be deleted.

If you want all duplicates no matter what row they are in deleted we can approach the task with a different macro. If a different macro is what you need repost and I will try to build you one. JSW


Sub DSRow()
'Find all the rows for which column "A" on Sheet1 = the same row & column on
'Sheet2 and delete that row from sheet1.
Application.ScreenUpdating = False
Worksheets("Sheet1").Select
Range("A1").Select
n = 1
n = n + 1
For Each r In Worksheets("Sheet1").UsedRange.Rows
n = r.Row
If Worksheets("Sheet1").Cells(n, 1) = Worksheets("Sheet2").Cells(n, 1) Then
Worksheets("Sheet1").Cells(n, 1).Select
Selection.EntireRow.Delete
End If
Next r
Application.ScreenUpdating = True
'Range("A1").Select
End Sub

[thexfactor44]

Wow! we're close i think. I need to be able to find if ANY value in cloumn A on sheet 1 = any value in column A on sheet 2...not row dependent...and if there is a match, delete that row from sheet 2.
so ie.:
sheet 1 a14="hello world"
sheet 2 a31412="hello world"

delete row 31412 on sheet 2.

thanks so much..i can tell we're close here...

[Jay Petrulis]

Hi,

I responded to your other thread.

Please don't start a new thread on the same topic. It makes it hard to follow what has already been requested/suggested, etc. Just continue with a follow-up in the same thread.

Bye,
Jay

[Joe Was]

I tested Jay's code and if I understand your desire, his code will work!

I am re-posting Jay's code here for you. Note: I did not see your other posts until Jay pointed it out. On your original post if you Reply back to your self adding more information about your question, your question will go to the top of the list and everyone will see it on the first page. Do not open a new question about a question you have on the board. It makes it hard for us answering. JSW

Sub Dtest()
Dim Rng1 As String, lastrow As Long

Application.ScreenUpdating = False

With Sheets("Sheet1")
Rng1 = Intersect(.UsedRange, .Range("A:A")).Address(True, True, xlR1C1)
End With

With Sheets("Sheet2")
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
.Range(.Cells(1, 2), .Cells(lastrow, 2)) = "=MATCH(RC[-1],Sheet1!" & Rng1 & ",0)"
.Rows(1).Insert
With .Cells(1, 2)
.Value = "Temp"
.AutoFilter Field:=2, Criteria1:="<>#N/A"
End With
.UsedRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Columns(2).Clear
End With

End Sub