Does anyone know how to create a loop?

Reply #9

KnuckleHead — Tue, 24 Jan 2012 19:27:32 GMT

Afternoon all,

To begin with, the column I want to remove the duplicates from is already sorted into an ascending order.

Below is a simple and elegant solution that I found in my e-mail. (I was told that my attempt was to complicated.)

Sub Duplicate_Remover

Do While Not Selection.CellDisplay =

Cell1 = .CellDisplay

.MoveCellPointer $Down,1

Cell2 = .CellDisplay

If Cell1 = Cell2 Then

.DeleteRows $Partial

.MoveCellPointer $Up,1

End If

Loop

End Sub

The sub is... [ More ]

Reply #8

KnuckleHead — Mon, 23 Jan 2012 02:04:26 GMT

Evening JADELottery,

I downloaded the test file and discovered that the entire code should have been placed into 1 module instead of 5 different modules. I setup a test range for each of the different sheets. It initially threw me on the 1st test when only the 0 disappeared. Then I pre-selected the range and tried again. It worked on all of the different examples.

Cool.

I still don't understand how to setup the loop I was originally working on, but this sub works great. And it does so i... [ More ]

Reply #7

JADELottery — Mon, 23 Jan 2012 01:25:35 GMT

Discovered another little quirk, but it should not affect the results too drastically.

Also, make sure you are copying and pasting all the lines of code, from Dim arry() As Double to End Function

It might be the version of Excel as well that could be affecting the code run.

Or you can download the test file here - http://www.jadexcode.com/RemoveDuplicatesTest.xls

I don't work with Excel VB that much either, just learned some of this yesterday and today.

__________

Dim arry() As... [ More ]

Reply #6

KnuckleHead — Mon, 23 Jan 2012 01:03:58 GMT

Hello JADELottery,

Thank you for the reply and the code.

Not being an experienced Excel user, I may have placed the coding incorrectly. I created 5 user modules in the This Workbook section of the VBA editor and pasted each of the 5 sets into a separate module. After saving the file, I attempted to run the RemoveDuplicates Sub. It popped up a message box titled Microsoft Visual Basic for Application box.

The message reads: Complile error: Sub or Function not defined.

The problem is i... [ More ]

Reply #5

JADELottery — Sun, 22 Jan 2012 18:51:43 GMT

We'll post the complete, corrected code one more time just so you don't have to copy two different sections.

__________

Dim arry() As Double

Sub RemoveDuplicates()

Randomize Timer

Dim SelectedRange As String

Dim RangeRowCount, r, i As Long

SelectedRange = ActiveWindow.RangeSelection.Address

RangeRowCount = Range(SelectedRange).Rows.Count

ReDim arry(RangeRowCount) As Double

For r = 1 To RangeRowCount

arry(r) = Val(Range(SelectedRange).Cells(r, 1).Value... [ More ]

Reply #4

JADELottery — Sun, 22 Jan 2012 16:10:28 GMT

did a copy and paste the the wrong code.

the RemoveDuplicates sub has an error, see correction below.

__________

Sub RemoveDuplicates()

Randomize Timer

Dim SelectedRange As String

Dim RangeRowCount, r, i As Long

SelectedRange = ActiveWindow.RangeSelection.Address

RangeRowCount = Range(SelectedRange).Rows.Count

ReDim arry(RangeRowCount) As Double

For r = 1 To RangeRowCount

arry(r) = Val(Range(SelectedRange).Cells(r, 1).Value)

Next

QuickSort 1, Ra... [ More ]

Reply #3

JADELottery — Sun, 22 Jan 2012 15:25:39 GMT

You could try this. It will work for any value, decimal or integer.

__________

Dim arry() As Double

Sub RemoveDuplicates()

Randomize Timer

Dim SelectedRange As String

Dim RangeRowCount, r, i As Long

SelectedRange = ActiveWindow.RangeSelection.Address

RangeRowCount = Range(SelectedRange).Rows.Count

ReDim arry(RangeRowCount) As Double

For r = 1 To RangeRowCount

arry(r) = Val(Range(SelectedRange).Cells(r, 1).Value)

Next

QuickSort 1, RangeRowCoun... [ More ]

Reply #2

KnuckleHead — Sat, 21 Jan 2012 16:34:46 GMT

Morning time*treat,

I got the sub last night. It took me a little while to comprehend which were the input and output columns. Once understood, it works great for 0 to 9 numbers.

Let me back up a bit. I have a file that contains several different games, from Pick 3 to Bonus games. I designed my sub to be generic so that it would work on different sheets and from different beginning locations. That's why there are no cell address in it. As long as the calling sub has positioned the cell poi... [ More ]

Reply #1

time*treat — Fri, 20 Jan 2012 22:19:29 GMT

I don't know if this hurt to write, but it certainly hurt to read.

I avoid the macro recorder whenever possible.

Fast code can be hard to read, but hard to read code isn't always fast.

Thus:

https://www.lotterypost.com/blogentry/62539

Does anyone know how to create a loop?

KnuckleHead — Fri, 20 Jan 2012 17:41:59 GMT

Morning all,

Does anyone know how to take the below sub and create a loop with it?

Begin with this column of numbers:

0

1

3

3

3

4

4

6

6

8

8

9

End with this column of numbers:

0

1

3

4

6

8

9

Here's a sub I've created. All of the sections work, but I don't know how to create the loop to keep it running.

Sub Duplicate_Remover

' This sub removes the duplicate numbers from a pre-selected single row range.

'

'be... [ More ]