Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loops to delete rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loops to delete rows
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loops to delete rows
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loops to delete rows
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loops to delete rows
If you do not delete from the bottom upward you are going to
have problems. See previous reply, there are examples there. .. "Mike Etzkorn" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loops to delete rows
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loops to delete rows
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 "Mike Etzkorn" wrote in message =... 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. = |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
How to Delete empty rows in excel in b/w rows with values | Excel Worksheet Functions | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
delete empty rows between rows with text | Excel Discussion (Misc queries) |