But still 9 times slower than mine <vbg

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Conan Kelly" wrote in message
...
Woops,

There were a couple of errors in that code I posted.

I corrected them and tested it and it looks like it works!!!

Option Explicit

Sub CreateCombinedList()
Dim prngItemKeys As Range
Dim prngCounties As Range
Dim prngItemKeyCell As Range
Dim prngCountyCell As Range
Dim pintCounter As Integer

Set prngItemKeys = ActiveSheet.Range("A1:A50")
Set prngCounties = ActiveSheet.Range("B1:B23")
pintCounter = 2

Worksheets("Sheet2").Range("A1") = "Item Key"
Worksheets("Sheet2").Range("B1") = "County"

For Each prngItemKeyCell In prngItemKeys.Cells
For Each prngCountyCell In prngCounties.Cells
Worksheets("Sheet2").Cells(pintCounter, 1) =
prngItemKeyCell.Value
Worksheets("Sheet2").Cells(pintCounter, 2) =
prngCountyCell.Value
pintCounter = pintCounter + 1
Next prngCountyCell
Next prngItemKeyCell


End Sub





"Conan Kelly" wrote in message
...
Steve,

Where do you want this list to be?

(be sure to make a copy of your file and work on the copy!!!)
(assuming you have the sheet that has these 2 lists as the active sheet
in the active workbook)
The following code will create the results you desire on a sheet named
"Sheet2" complete with column headings (I THINK!!! I have not tested it.)

Option Explicit

Sub CreateCombinedList()
Dim prngItemKeys As Range
Dim prngCounties As Range
Dim prngItemKeyCell As Range
Dim prngCountyCell As Range
Dim pintCounter As Integer

prngItemKeys = ActiveSheet.Range("A1:A50")
prngCounties = ActiveSheet.Range("B1:B23")
pintCounter = 2

Worksheets("Sheet2").Range("A1") = "Item Key"
Worksheets("Sheet2").Range("B1") = "Item Key"

For Each prngItemKeyCell In prngItemKeys.Cells
For Each prngCountyCell In prngCounties.Cells
Worksheets("Sheet2").Cells(pintCounter, 1) =
prngItemKeyCell.Value
Worksheets("Sheet2").Cells(pintCounter, 2) =
prngCountyCell.Value
pintcoutner = pintCounter + 1
Next prngCountyCell
Next prngItemKeyCell


End Sub

HTH,

Conan






"Steve" wrote in message
...
a1:a50 is a list of item keys. b1:b23 is a list of counties. I want to
create a two column list (it would have 1150 rows: 50 x 23). It would
have
every county for each item key.