Author Topic: VBA in Excel  (Read 4795 times)

0 Members and 1 Guest are viewing this topic.

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: 12333
  • 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: 12333
  • 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 »