I have a block of cells from row 2 to 37. The information of interest starts at column G to column CI. In each odd numbered row is a list of names. I need to count the number of different names in each odd numbered row disregarding blank cells in the row and return those values to a column in another sheet in the same workbook. For example:
Row 3 - Mike, Mike, Kevin, ,Tammy would return a value of 3 where each name is in a separate cell with a blank cell between kevin and tammy.
Any help is greatly appreciated.
Erik

Your request lacks clarity, but the following should get
close to what you want.

HTH,
Merjet


Sub Macro1()
Dim colNames As Collection
Dim c As Range
Dim rng As Range
Dim iCt As Integer
Dim iRow As Integer

Sheets("Sheet2").Cells.Clear
On Error Resume Next
For iRow = 3 To 37 Step 2
Set colNames = New Collection
Set rng = Sheets("Sheet1").Range("G" & iRow & ":CI" & iRow)
For Each c In rng
Debug.Print c.Address
colNames.Add c.Value, c.Value
Next c
For iCt = 1 To colNames.Count
Sheets("Sheet2").Cells(iCt, (iRow - 1) / 2) = colNames(iCt)
Next iCt
Set colNames = Nothing
Next iRow

End Sub

"merjet" wrote:

Your request lacks clarity, but the following should get
close to what you want.

HTH,
Merjet


Sub Macro1()
Dim colNames As Collection
Dim c As Range
Dim rng As Range
Dim iCt As Integer
Dim iRow As Integer

Sheets("Sheet2").Cells.Clear
On Error Resume Next
For iRow = 3 To 37 Step 2
Set colNames = New Collection
Set rng = Sheets("Sheet1").Range("G" & iRow & ":CI" & iRow)
For Each c In rng
Debug.Print c.Address
colNames.Add c.Value, c.Value
Next c
For iCt = 1 To colNames.Count
Sheets("Sheet2").Cells(iCt, (iRow - 1) / 2) = colNames(iCt)
Next iCt
Set colNames = Nothing
Next iRow

End Sub


I put that into sheet1 as a worksheet change sub and it returns all the different names in each row as a column in sheet2. All I really want to know is how many different people are in each odd numbered row. So if row 3 has a list of names with only 4 distinctly different names in the list, the number 4 is returned to cell a1 in sheet2. Then if row 5 in sheet1 has 3 different names, the number 3 is returned to a2 of sheet2 and so on through row 37.

All I really want to know is how many different people are in each odd
numbered row. So if row 3 has a list of names with only 4 distinctly
different
names in the list, the number 4 is returned to cell a1 in sheet2. Then if
row 5
in sheet1 has 3 different names, the number 3 is returned to a2 of sheet2
and
so on through row 37.

Then replace:
For iCt = 1 To colNames.Count
Sheets("Sheet2").Cells(iCt, (iRow - 1) / 2) = colNames(iCt)
Next iCt

With:
Sheets("Sheet2").Cells(iCt, (iRow - 1) / 2, "A") = colNames.Count

HTH,
Merjet

Here is the final product. Thank you so much for your help.
Erik

Private Sub Worksheet_Change(ByVal Target As Range)
Dim colNames As Collection
Dim c As Range
Dim rng As Range
Dim iCt As Integer
Dim iRow As Integer

On Error Resume Next
For iRow = 3 To 37 Step 2
Set colNames = New Collection
Set rng = Sheets("Tracker").Range("G" & iRow & ":CI" & iRow)
For Each c In rng
Debug.Print c.Address
colNames.Add c.Value, c.Value
Next c
Sheets("Student Info").Cells(((iRow - 1) / 2) + 3, 9) = colNames.Count
Set colNames = Nothing
Next iRow
End Sub