Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
output unique records to a dynamic range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
output unique records to a dynamic range
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 On 19 Sep., 23:31, MJKelly wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
output unique records to a dynamic range
Hello Matt,
Maybe my UDF lfreq is a good example for you: http://www.sulprobil.com/html/listfreq.html Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filer for unique records and return all column data for unique rec | Excel Discussion (Misc queries) | |||
unique records in specified range | Excel Worksheet Functions | |||
Dynamic "Unique" Records (no blanks) | Excel Discussion (Misc queries) | |||
unique filter results in some non-unique records. | Excel Discussion (Misc queries) | |||
Counting unique records based on date range | Excel Worksheet Functions |