Looping Question When Deleting Rows

[jerm_e]

For some reason, when I execute this query (to compare values in columns G and J), the loop will stop once it finds two matching values. This is driving me crazy! Any help or guidance would be greatly appreciated.

Sub FindDuplicates()

ActiveSheet.Cells.Select
Dim i As Long
For i = Selection.Rows.Count To 1 Step -1
If ActiveCell.Offset(0, 6).Value <> ActiveCell.Offset(0, 9).Value Then
ActiveCell.EntireRow.Delete
End If
Next i
End Sub

 

[Gary McMaster]

I haven't been able to find it in help (again) but once read that you can't change the number of elements in a collection while you are iterating that collection.

I'm not sure if that applies here but you are changing the terminal variable in your "for / next" statement (selection.rows.count) when you delete a row from that collection while still inside the loop.

You may want to try building a range to be deleted after you exit the loop instead of deleting the row while still in the loop.

Good luck.

Gary

 

[Boller]

Your code is selecting all the cells. A1 is therefore the active cell.

Your loop is starting at the active cell, so it never gets to a row other than row 1.

 

[Chris Bode]

Use following code instead

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml>  <w:WordDocument>   <w:View>Normal</w:View>   <w:Zoom>0</w:Zoom>   <w:PunctuationKerning/>   <w:ValidateAgainstSchemas/>   <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>   <w:IgnoreMixedContent>false</w:IgnoreMixedContent>   <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>   <w:Compatibility>    <w:BreakWrappedTables/>    <w:SnapToGridInCell/>    <w:WrapTextWithPunct/>    <w:UseAsianBreakRules/>    <w:DontGrowAutofit/>   </w:Compatibility>   <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>  </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml>  <w:LatentStyles DefLockedState="false" LatentStyleCount="156">  </w:LatentStyles> </xml><![endif]--><style> <!--  /* Style Definitions */  p.MsoNormal, li.MsoNormal, div.MsoNormal 	{mso-style-parent:""; 	margin:0in; 	margin-bottom:.0001pt; 	mso-pagination:widow-orphan; 	font-size:12.0pt; 	font-family:"Times New Roman"; 	mso-fareast-font-family:"Times New Roman";} @page Section1 	{size:8.5in 11.0in; 	margin:1.0in 1.25in 1.0in 1.25in; 	mso-header-margin:.5in; 	mso-footer-margin:.5in; 	mso-paper-source:0;} div.Section1 	{page:Section1;} --> </style><!--[if gte mso 10]> <style>  /* Style Definitions */  table.MsoNormalTable 	{mso-style-name:"Table Normal"; 	mso-tstyle-rowband-size:0; 	mso-tstyle-colband-size:0; 	mso-style-noshow:yes; 	mso-style-parent:""; 	mso-padding-alt:0in 5.4pt 0in 5.4pt; 	mso-para-margin:0in; 	mso-para-margin-bottom:.0001pt; 	mso-pagination:widow-orphan; 	font-size:10.0pt; 	font-family:"Times New Roman"; 	mso-ansi-language:#0400; 	mso-fareast-language:#0400; 	mso-bidi-language:#0400;} </style> <![endif]-->  Sub FindDuplicates()
ActiveSheet.Cells.Select
Dim i As Long<o:p></o:p>
  <o:p> </o:p>
  For i = Selection.Rows.Count To 1 Step -1<o:p></o:p>
    If Sheet1.Cells(i, 7).Value <> Sheet1.Cells(i, 10).Value Then<o:p></o:p>
  <o:p> </o:p>
  Sheet1.Rows(i).Delete<o:p></o:p>
  End If<o:p></o:p>
  Next i
End Sub

 

[Boller]

Use following code instead

<META content=Word.Document name=ProgId><META content="Microsoft Word 11" name=Generator><META content="Microsoft Word 11" name=Originator><LINK href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml" rel=File-List><STYLE> <!--  /* Style Definitions */  p.MsoNormal, li.MsoNormal, div.MsoNormal     {mso-style-parent:"";     margin:0in;     margin-bottom:.0001pt;     mso-pagination:widow-orphan;     font-size:12.0pt;     font-family:"Times New Roman";     mso-fareast-font-family:"Times New Roman";} @page Section1     {size:8.5in 11.0in;     margin:1.0in 1.25in 1.0in 1.25in;     mso-header-margin:.5in;     mso-footer-margin:.5in;     mso-paper-source:0;} div.Section1     {page:Section1;} --> </STYLE> Sub FindDuplicates()
ActiveSheet.Cells.Select
Dim i As Long<?xml:namespace prefix = o /><o:p></o:p>
  <o:p> </o:p>
  For i = Selection.Rows.Count To 1 Step -1<o:p></o:p>
    If Sheet1.Cells(i, 7).Value <> Sheet1.Cells(i, 10).Value Then<o:p></o:p>
  <o:p> </o:p>
  Sheet1.Rows(i).Delete<o:p></o:p>
  End If<o:p></o:p>
  Next i
End Sub

In case Sheet1 is not the active sheet :-

Sub FindDuplicates()
ActiveSheet.Cells.Select
Dim i As Long
For i = Selection.Rows.Count To 1 Step -1
    If Cells(i, 7) <> Cells(i, 10) Then Rows(i).Delete
Next i
End Sub

Or, a little more efficient :-

Sub FindDuplicates()
Dim rws&, i&
rws = Range([A1], ActiveSheet.UsedRange).Rows.Count
For i = rws To 1 Step -1
    If Cells(i, 7) <> Cells(i, 10) Then Rows(i).Delete
Next i
End Sub