output unique records to a dynamic range

[MJKelly]

Hi,

I am trying to make the code below work. It is attempting to look
thorugh a range and copy all of the different types into another
range. This second range expands as the amount of different types
increase, so it starts with one cell (a copy of the first cell in the
types range). The "type" range is compared to the "output" range.

This will provide me with a list of the different types which can use
as sheet names.

Sub Dynamic_Signon_Areas()

Dim c As Range
Dim r As Range

Dim myOffset As Integer
myOffset = 0

With ThisWorkbook.Sheets("Dynamic Signon Areas")
.Range("B1").Value = Sheets("Dynamic Signon
Areas").Range("A1").Value
End With

For Each c In Sheets("Dynamic Signon Areas").Range("A1:A10")
For Each r In Sheets("Dynamic Signon Areas").Range("B1:
& .offset(myoffset,0)") 'cant get the range to expand
If Not c.Value = r.Value Then
r.End(xlUp).Offset(myOffset + 1, 0).Value = c.Value
myoffset = myoffset + 1
Exit For 'should I exit for?
End If
Next r
Next c


End Sub

Kind regards,
Matt

 

[Per Jessen]

Hi Matt

For Each r In Sheets("Dynamic Signon Areas").Range("B1",
Range("B1").End(xlDown))

or

For Each r In Sheets("Dynamic Signon Areas").Range("B1:B" & myOffset)

But you won't get the desired output. Insert a label in A1 and try
this:

Sub Dynamic_Signon_Areas()
Dim FilterRange As Range

Set FilterRange = Range("A1", Range("A1").End(xlDown))

FilterRange.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("B1"), Unique:=True
End Sub

Regards,
Per

 

[Bernd P]

Hello Matt,

Maybe my UDF lfreq is a good example for you:
http://www.sulprobil.com/html/listfreq.html

Regards,
Bernd