Loops to delete rows

[Guest]

Here is a quick version of the situation. I have a file with 7 columns of data and I sort it with a script. Then I have another script that goes through and deletes files based on a certain paramenter. If the value in "A1" = "A2" and "D1" = "D2" then delete the row "2" as long as the value in "C2" is less than the value in "C1" - then contine on throught the entire range deleting rows

So far the sort works and I think I am close on the loop - using two books and info found in here - but I am not sure whats wrong and why it isn't working right. Here's the code I have so far

Option Explici
Public i As Range, Startingi As Rang
Public Brand As String, Type As String, Quantity As Intege

Sub DeletePairs(
Dim SortRange As Rang
Set SortRange = Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(0, 7).
Address
Set Startingi =
Type = i.Valu
Brand = i.Offset(0, 1).Valu
Do While Not (i.Offset(0, 0) = i.Offset(1, 0) & i.Offset(0, 1) = i.Offset(1, 1)
If i.Offset(0, 2) > i.Offset(1, 2) The
i.Offset(1).EntireRow.Delet
End I
Loo
End Su

I am new to this type of scripting, I really only know the basics - so I am not sure if I am even on the right path - It only what I have sort of pieced together so far

Any help would be greatly appreciated, because the frustration level is starting to get hig
Mike Etzkor

 

[Don Guillett]

when deleting rows it is best to work from the bottom up with
for i=100 to 1 step -1
next

--
Don Guillett
SalesAid Software
(e-mail address removed)

Here is a quick version of the situation. I have a file with 7 columns of

data and I sort it with a script. Then I have another script that goes
through and deletes files based on a certain paramenter. If the value in
"A1" = "A2" and "D1" = "D2" then delete the row "2" as long as the value in
"C2" is less than the value in "C1" - then contine on throught the entire
range deleting rows.

So far the sort works and I think I am close on the loop - using two books

and info found in here - but I am not sure whats wrong and why it isn't
working right. Here's the code I have so far:

Option Explicit
Public i As Range, Startingi As Range
Public Brand As String, Type As String, Quantity As Integer

Sub DeletePairs()
Dim SortRange As Range
Set SortRange = Range("A1", Range("A" &

Rows.Count).End(xlUp).Offset(0, 7). _

Address)
Set Startingi = i
Type = i.Value
Brand = i.Offset(0, 1).Value
Do While Not (i.Offset(0, 0) = i.Offset(1, 0) & i.Offset(0, 1) = i.Offset(1, 1))
If i.Offset(0, 2) > i.Offset(1, 2) Then
i.Offset(1).EntireRow.Delete
End If
Loop
End Sub

I am new to this type of scripting, I really only know the basics - so I

am not sure if I am even on the right path - It only what I have sort of
pieced together so far.

 

[David McRitchie]

Hi Mike,
I don't see the value of i changing which is supposed to be
a row number. When you delete rows (or insert rows) it is best
to work from the bottom so you don't trip over yourself skipping
a row because you deleted the current row just before going to
the next row.

Take a look at the following and see if this helps, they don't
have the part about comparing column A and D, but since
you know about OFFSET you should not have too much of a problem.

In the #rows section of

 

[Guest]

Ok, I did a little more research and I think I almost have it - here is the problem. The script I have deletes all the rows with a lower quantity in column "C" with duplicate information in columns "A" and "D". All that works but then the programn doesn't exit when it is done, instead I get an error, so somewhere it isn't leaving the loop, I think. I could really use some help on this one, it's almost there. Here is all the code, its kind of long

P.S. I have data several hundred rows long and 7 columns wide with lots of duplicates, if that is important

Option Explici
Public i As Range, Startingi As Range, IdenticalValues As Rang
Public Inventory As String, Brand As String, Quantity As Intege
Sub DoItAll(
SortDat
DeleteInf
End Su
Sub SortData(
Dim Total As Lon
Dim SortRange As Rang
Set SortRange = Range("A1", Range("A" & Rows.Count).
End(xlUp).Offset(0, 7).Address
SortRange.Sort Key1:=Range("D1"), Order1:=xlAscending,
Key2:=Range("A1"), Order2:=xlAscending, Key3:=Range("C1"), Order3:=xlDescending, Header:=xlYes,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBotto
Total = SortRange.Rows.Coun
Set i = Range("A2"
End Su
Sub DeleteInfo(
Do Until i.Value = "
GetIdenticalInf
SortRangeForQuantit
DelDuplicate
Loo
End Su
Sub GetIdenticalInfo(
GetNextPair
Set Startingi =
Brand = i.Offset(0, 3).Valu
Inventory = i.Valu
Do Until Not (i = i.Offset(1) And i.Offset(0, 3) = i.Offset(1, 3)
Set i = i.Offset(1
Loo
Set IdenticalValues = Range(Startingi, i.Offset(0, 7)
If IdenticalValues.Rows.Count = 1 The
Set i = i.Offset(1
GoTo GetNextPai
End I
Set i = Starting
End Su
Sub SortRangeForQuantity(
IdenticalValues.Sort Key1:=i.Offset(0, 1), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBotto
Quantity = i.Offset(0, 2).Valu
End Su
Sub DelDuplicates(
Do Until (i.Offset(1, 3).Value <> Brand Or
i.Offset(1, 0).Value <> Inventory

If i.Offset(0, 2) > i.Offset(1, 2) The
i.Offset(1).EntireRow.Delet
Els
Set i = i.Offset(1
End I

Set i = i.Offset(1
Quantity = i.Offset(0, 2).Valu
Loo
Set i = Starting
End Su

Thanks for any help on this as I am doing well but this type of script is still somewhat more complicted that I am completely comfortable with

Mike Etzkor

 

[David McRitchie]

If you do not delete from the bottom upward you are going to
have problems. See previous reply, there are examples there.
..

Ok, I did a little more research and I think I almost have it - here is the problem. The script I have deletes all the rows with

a lower quantity in column "C" with duplicate information in columns "A" and "D". All that works but then the programn doesn't exit
when it is done, instead I get an error, so somewhere it isn't leaving the loop, I think. I could really use some help on this one,
it's almost there. Here is all the code, its kind of long.

 

[Guest]

Ok, I looked through what you have and I get what you are saying about deleting from the bottom up, I think. What I have does the sort and then goes down row by row and does the deletes, I am just not sure how to reverse the process to delete from the bottom up. I understand some of the basics of VBA but some of this is at the limits of what I understand, I just pulled pieces of thing from the two books I have and this site and my own basic knowledge, and I have gotten this far, but if you could be a little more specific with how to modify what I have to reverse the process it would be helpful. I am not looking for just answers, because this is somehting I am trying to learn and become more proficient at, I want to understand how and why, I just need a little extra guidance

Thanks

Mike

 

[David McRitchie]

Hi Mike,
In the link I supplied with my first reply in this thread you will see
Step -1 which decrements the counter.
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

Ok, I looked through what you have and I get what you are saying about
deleting from the bottom up, I think. [clipped] I want to understand how
and why, I just need a little extra guidance.

=