PDA

View Full Version : Help with VB in Excel 2010



Denice B
06-26-2012, 09:27 AM
I am new to this programming stuff and still have a lot to learn I need help to query an array and remove the duplicate values. In essence I need to build the array and loop through it to only count the distinct values so that I can write them to another spreadsheet. Here is the beginning of my code I just don't know where to go from here.

Basically the data sheet I am pulling from has an ubound of 251 of the 251 I only need 94 items.

Dim loc() As String
Dim i As Integer
Dim row As Integer

row = 2

For i = 0 To count
Do While Sheets("Data").Cells(row, 4) <> ""
row = row + 1
Loop
count = row - 2
Next

ReDim Preserve loc(count)

I know I need to create a second unique array but how do I only add the distinct values to it. Please someone help... I am getting very frustrated and on a deadline.

Thanks in Advance

CodeNinja
06-26-2012, 09:43 AM
Denise B,
Welcome to the forum. It sounds to me like you are trying to hang a picture frame with a nail and a sledge hammer... There is likely a much simpler solution for your needs.

It would help a lot if you gave a very small sampling of what you want done (before and after) 3 or 4 rows of data should be more than enough...

Jan Karel Pieterse
06-26-2012, 09:44 AM
It is probably easiest if you use the advanced filter to copy the table with duplicates to a temporary sheet and read the resulting table into your variable.

Denice B
06-26-2012, 10:19 AM
Thanks for the fast response here is the data that I am pulling in:

Column A2 - Employee Name
Column B2 - Team Name
Column C2 - Region Name
Column D2 - Location Name

Every employee has a location, team and Region name and there are multiple employees in each region, team, and location. What I want to do is scan the data find the distinct values in the location field (Which is 94) for now but we are always changing and adding locations, so it needs to be where the code will accept the change without re-writing it every time.

I know it sounds like I am trying to hang a picture with a sledgehammer but I am open to suggestions. I just didn't know of a better way to do it with VB.

Thanks again in advance for your help.

Aussiebear
06-26-2012, 11:38 AM
Create dynamic ranges for each of the columns. Then follow the advice offered by JKP in Post #3

Kenneth Hobs
06-26-2012, 06:59 PM
When you say arrays, do you mean arrays or ranges?

For ranges in Sheet 1 for A2:D end row:
Sub CopyNoDups()
Dim r As Range
Set r = Sheet1.Range("A2:D" & Sheet1.Range("A" & Rows.Count).End(xlUp).Row)
r.Copy Sheet2.Range("A2")
Sheet2.UsedRange.RemoveDuplicates 4, xlNo
End Sub