Author Topic: VBA in Excel  (Read 4634 times)

0 Members and 1 Guest are viewing this topic.

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
VBA in Excel
« 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?
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #1 on: January 23, 2008, 09:18:52 AM »
Quote
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
« Last Edit: January 23, 2008, 09:24:34 AM by CmdrDuh »
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Guest

  • Guest
Re: VBA in Excel
« Reply #2 on: January 23, 2008, 09:22:56 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?


Huh?  Purge as in like, selecting everything and pressing the DEL key??  :?

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #3 on: January 23, 2008, 09:26:56 AM »
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?
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Guest

  • Guest
Re: VBA in Excel
« Reply #4 on: January 23, 2008, 09:30:41 AM »
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).   :-)

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #5 on: January 23, 2008, 09:31:31 AM »
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
« Last Edit: January 23, 2008, 09:36:39 AM by CmdrDuh »
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #6 on: January 23, 2008, 09:37:14 AM »
Sorry it took so long, I didn't know you couldn't post xls file directly. 
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Guest

  • Guest
Re: VBA in Excel
« Reply #7 on: January 23, 2008, 09:40:53 AM »
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?

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #8 on: January 23, 2008, 09:44:47 AM »
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.
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #9 on: January 23, 2008, 09:45:53 AM »
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. 
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Greg B

  • Seagull
  • Posts: 12313
  • Tell me a Joke!
Re: VBA in Excel
« Reply #10 on: January 23, 2008, 10:19:53 AM »
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.

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #11 on: January 23, 2008, 10:21:24 AM »
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?
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Greg B

  • Seagull
  • Posts: 12313
  • Tell me a Joke!
Re: VBA in Excel
« Reply #12 on: January 23, 2008, 11:05:52 AM »
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


Greg B

  • Seagull
  • Posts: 12313
  • Tell me a Joke!
Re: VBA in Excel
« Reply #13 on: January 23, 2008, 11:06:57 AM »
First hard code, then modify to allow for variables.

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #14 on: January 23, 2008, 11:09:32 AM »
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
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Guest

  • Guest
Re: VBA in Excel
« Reply #15 on: January 23, 2008, 11:10:02 AM »
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...

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #16 on: January 23, 2008, 11:20:25 AM »
giving it a whirl now
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Guest

  • Guest
Re: VBA in Excel
« Reply #17 on: January 23, 2008, 11:29:45 AM »
Hope you have a backup copy (just in case).

Let me know how it works for you!

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #18 on: January 23, 2008, 11:32:59 AM »
I had to change it a little, but this seems to work
Code: [Select]
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
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #19 on: January 23, 2008, 11:33:36 AM »
Although it stopped at 15875, I wonder is that the limit of an integer.  I cant remember off the top of my head
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Guest

  • Guest
Re: VBA in Excel
« Reply #20 on: January 23, 2008, 11:36:40 AM »
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...
Code: [Select]
MsgBox intEnd
...after this line...
Code: [Select]
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?

Greg B

  • Seagull
  • Posts: 12313
  • Tell me a Joke!
Re: VBA in Excel
« Reply #21 on: January 23, 2008, 11:59:41 AM »
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.

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #22 on: January 23, 2008, 12:20:15 PM »
Code: [Select]
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
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #23 on: January 23, 2008, 12:21:06 PM »
I also turned the Application.Update = False to True so I could watch, as there was no indication it was doing anything
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Greg B

  • Seagull
  • Posts: 12313
  • Tell me a Joke!
Re: VBA in Excel
« Reply #24 on: January 23, 2008, 12:22:01 PM »
Do you REALLY need to have it loop through 6 times?

Guest

  • Guest
Re: VBA in Excel
« Reply #25 on: January 23, 2008, 12:25:34 PM »
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.

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #26 on: January 23, 2008, 01:19:11 PM »
After I made the changes I did, the 6 time loop is unnecessary
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #27 on: January 23, 2008, 01:20:11 PM »
BTW, thanks everyone for the input.  I learned something new today, and thats a good thing
« Last Edit: January 23, 2008, 02:50:37 PM by CmdrDuh »
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Guest

  • Guest
Re: VBA in Excel
« Reply #28 on: January 23, 2008, 01:37:58 PM »
So what was the final tally?  How many duplicate rows did you eliminate?

Guest

  • Guest
Re: VBA in Excel
« Reply #29 on: January 23, 2008, 01:38:14 PM »
BTW, thanks everyone for the input.  I learned something new today, and thats a good thing
As did I!
« Last Edit: January 23, 2008, 02:50:50 PM by CmdrDuh »

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #30 on: January 23, 2008, 02:15:07 PM »
18103
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Guest

  • Guest
Re: VBA in Excel
« Reply #31 on: January 23, 2008, 02:17:21 PM »
WOW!   :-o

Dnereb

  • Guest
Re: VBA in Excel
« Reply #32 on: January 25, 2008, 09:11:18 AM »
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.

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #33 on: January 25, 2008, 09:14:39 AM »
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.
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

ML

  • Guest
Re: VBA in Excel
« Reply #34 on: January 25, 2008, 04:03:09 PM »
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

Dnereb

  • Guest
Re: VBA in Excel
« Reply #35 on: January 26, 2008, 03:31:06 PM »
Actually it doesn't test again after deletion:

Snippet of solution code:
 
Code: [Select]
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
Code: [Select]
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)

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #36 on: January 28, 2008, 09:19:48 AM »
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.
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

ML

  • Guest
Re: VBA in Excel
« Reply #37 on: January 28, 2008, 09:27:47 AM »

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