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 |
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 |
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 |
All times are GMT +1. The time now is 08:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com