TheSwamp
Code Red => VB(A) => Topic started by: David Hall on January 23, 2008, 09:18:05 AM
-
I have never written anything in VBA for excel. that being said, I could use some help with a function to purge a spreadsheet. Any takers?
-
000011815 UNS Electric Inventory STICK SHOT GUN CHANCE SINGLE Each DCLASS EF-KSAFETY 1 7 $202.57
000011815 UNS Electric Inventory STICK SHOT GUN CHANCE SINGLE Each DCLASS EF-LSAFETY 3 7 $202.57
000011815 UNS Electric Inventory STICK SHOT GUN CHANCE SINGLE Each DCLASS EF-NSAFETY 4 7 $202.57
000011816 UNS Electric Inventory GRIP SMALL Each DCLASS EF-KSAFETY 3 7 $79.60
000011816 UNS Electric Inventory GRIP SMALL Each DCLASS EF-LSAFETY 12 7 $79.60
000011816 UNS Electric Inventory GRIP SMALL Each DCLASS EF-NSAFETY 2 7 $79.60
-
I have never written anything in VBA for excel. that being said, I could use some help with a function to purge a spreadsheet. Any takers?
Huh? Purge as in like, selecting everything and pressing the DEL key?? :?
-
Purge as in like, selecting everything and pressing the DEL key?? :?
Yes hahaha,
OK no, I need to search Column A for each number, like 11815 in above example, then go over to the H column and compare the $$ amount to the one below. Delete the row which is < the other. Make sense?
-
Purge as in like, selecting everything and pressing the DEL key?? :?
Yes hahaha,
OK no, I need to search Column A for each number, like 11815 in above example, then go over to the H column and compare the $$ amount to the one below. Delete the row which is < the other. Make sense?
Yup. Can you post a small example, like what you have above (only in XLS format, of course). :-)
-
I am trying to figure out how to grab A# and A#+1 . From there the > comparison is pretty easy. Deleting the row is the next challenge. I also thought about using N (integer) to keep up with where I am row.count wise
Edit: Attached XLS file
-
Sorry it took so long, I didn't know you couldn't post xls file directly.
-
Purge as in like, selecting everything and pressing the DEL key?? :?
Yes hahaha,
OK no, I need to search Column A for each number, like 11815 in above example, then go over to the H column and compare the $$ amount to the one below. Delete the row which is < the other. Make sense?
What if, like in your example, the dollar amounts are the same?
-
Delete one of the rows. This is a very small part of an inventory/vendor spreadsheet that our warehouse has with the people that order the parts. So there might be 2 vendors with the same price, or 10 w/ the same, or 10 and 9 have the same price, but 1 is higher. Im trying to narrow it down for the upcoming budget season so I can use the biggest amount to CYA in the budget.
-
BTW, this SS is 35000+ lines long and I spent 4 hours yesterday manually deleting the extra rows. I only made it through ~1000 before I went crazy.
-
A good starting point is to record a macro. Then you can modify it to fit your needs.
Basically start recording and do it manually what you want done and take a look at the code.
-
How do you record something when the process takes place in your mind? How do you look at 2 rows, and delete the smaller one?
-
How do you record something when the process takes place in your mind? How do you look at 2 rows, and delete the smaller one?
This of course is where it gets modified.
At least you'll have to code to grab a row and delete it. You throw in an
if x > y then
rows.delete yadda
-
First hard code, then modify to allow for variables.
-
ok, now I see where your going. The problem is I know very little about VBA in excel. How do I grab a row? Im thinking there should be an activerow, but I haven't found it yet. Once I figure out how to grab a row, the > logic should be pretty straightforward
-
ok, now I see where your going. The problem is I know very little about VBA in excel. How do I grab a row? Im thinking there should be an activerow, but I haven't found it yet. Once I figure out how to grab a row, the > logic should be pretty straightforward
Try this...
-
giving it a whirl now
-
Hope you have a backup copy (just in case).
Let me know how it works for you!
-
I had to change it a little, but this seems to work
Sub DelDups()
Dim intEnd As Long
Dim intRow As Long
Dim intColumn As Long
Dim i As Integer
' Turn off screen updating so we don't see all of the "flashing" as the sheet is being worked on
'Application.ScreenUpdating = False
intEnd = Application.CountA(ActiveSheet.Range("A:A")) 'Get the total number of rows used
intRow = 2
intColumn = 1
For i = 1 To 6 ' Loop through 6 times, just to be sure we get all of the duplicate entries
For intRow = 4 To intEnd
' If the value of one cell is = to the value of the cell above it...
While Cells(intRow, intColumn) = Cells(intRow + 1, intColumn)
' If the value of the cell in column H is < or = to the one above it...
If Cells(intRow, 8) <= Cells(intRow + 1, 8) Then
' Select the row...
Rows(intRow).Select
' And delete it!!
Selection.Delete
End If
' Increase the row counter by 1
'intRow = intRow + 1
Wend
Next intRow
Next i
' Turn on screen updating
Application.ScreenUpdating = True
MsgBox "All Done"
End Sub
-
Although it stopped at 15875, I wonder is that the limit of an integer. I cant remember off the top of my head
-
Although it stopped at 15875, I wonder is that the limit of an integer. I cant remember off the top of my head
Just for ha-ha's.
Add this...
MsgBox intEnd
...after this line...
intEnd = Application.CountA(ActiveSheet.Range("A:A")) 'Get the total number of rows used
...and see what pops up.
Comment out the rest of the code. Compare the number in the message box to the last row of the spreadsheet. Are they the same?
-
ok, now I see where your going. The problem is I know very little about VBA in excel. How do I grab a row? Im thinking there should be an activerow, but I haven't found it yet. Once I figure out how to grab a row, the > logic should be pretty straightforward
The recorded macro would have shown you.
-
Option Explicit
Sub DelDups()
Dim intEnd As Long
Dim intRow As Long
Dim intColumn As Long
Dim i As Integer
' Turn off screen updating so we don't see all of the "flashing" as the sheet is being worked on
'Application.ScreenUpdating = False
intEnd = Application.CountA(ActiveSheet.Range("A:A")) 'Get the total number of rows used
intRow = 4
intColumn = 1
For i = 1 To 6 ' Loop through 6 times, just to be sure we get all of the duplicate entries
For intRow = 4 To intEnd
Rows(intRow).Select
' If the value of one cell is = to the value of the cell above it...
While Cells(intRow, intColumn) = Cells(intRow + 1, intColumn)
' If the value of the cell in column H is < or = to the one above it...
If Cells(intRow, 8) <= Cells(intRow + 1, 8) Then
' Select the row...
Rows(intRow).Select
' And delete it!!
Selection.Delete
Else
' Select the row...
Rows(intRow + 1).Select
' And delete it!!
Selection.Delete
End If
' Increase the row counter by 1
'intRow = intRow + 1
Wend
Next intRow
Next i
' Turn on screen updating
Application.ScreenUpdating = True
MsgBox "All Done"
End Sub
OK, I had to take into account if row+1 was > row, so it was freezing at the first instance of said problem. I added the else statement to delete r+1 and it flew throught the rest of the SS
-
I also turned the Application.Update = False to True so I could watch, as there was no indication it was doing anything
-
Do you REALLY need to have it loop through 6 times?
-
Do you REALLY need to have it loop through 6 times?
Probably not, but when I tried it, it deleted MOST of the duplicate entries. I ran it again, and it deleted a few more duplicate entries. I'm not quite sure WHY it was acting like that, because the code is telling it to look at the current line and compare it with the one above it, so unless something isn't registering right away with Excel....??!? I dunno.
-
After I made the changes I did, the 6 time loop is unnecessary
-
BTW, thanks everyone for the input. I learned something new today, and thats a good thing
-
So what was the final tally? How many duplicate rows did you eliminate?
-
BTW, thanks everyone for the input. I learned something new today, and thats a good thing
As did I!
-
18103
-
WOW! :-o
-
The reason for doing the loop 6 times is simple in itself...
If you delete a row you are on.... you are on the next row (the original is gone)
if you loop on and move to the next row... You just skipped one!
solution run your loop from end to start instead.
-
actually, when you delete the row your on, the one below moves up and your on it. The test still runs and tests the now current row and the new one below it.
-
CM
I have not read the entire post; I'm just weighing in here
There a few methods you can use
xlshiftup (down)
And the row object (like cells) may have an offsett method as well.
Mark
-
Actually it doesn't test again after deletion:
Snippet of solution code:
Rows(intRow).Select
' And delete it!!
Selection.Delete
End If
' Increase the row counter by 1
'intRow = intRow + 1
Ml is correct as well using xlshiftup(down) will prevent this logic error as well.
but If I had to choose I would prefer looping end to start end inspecting the row below
If Cells(intRow, 8) >= Cells(intRow - 1, 8) ThenThis will reduce the instructions by 1 and in a loop run this often that is a legal argument to me.
(edit: added code tags to single line of code)
-
Actually it doesn't test again after deletion:
Actually it does, in fact it will continue to test until the 2 lines dont match. That is the purpose of the while/wend. I guess i should remove the extra comments from the code as it looks like it is confusing people about when the counter is being incremented.
-
I wish there was a row and column (delete,insert) event.
I was trying to make something happen for weeks in my one spread sheet.
I was on The MSDN Forums and all; I got a lot of great suggestions but was still never satisfied with the result.
The one thing I did l learn and as someone in the forum also suggested; that is using a named range when you know that rows or columns are likely to be inserted; this allows a dynamic update to your range.
I think I am a bit off topic here; sorry, I am just thinking out loud.
I could probably do something with NamedRange.usedRange
If the NamedRange.UsedRange = +1 or something like that.
Named Ranges are priceless in Excel