Thisworkbook refers to the workbook that contains the code. If you only
have one workbook open, then that should be the workbook containing the
code.


--
Regards,
Tom Ogilvy

"Rowan" wrote in message
...
Hi Tom

This looks like a very neat way of doing this calculation so I was playing
around with it to make sure I understand how it works.

I was getting strange results (no matter how much data I had on the sheet
the range evaluated was always B2:B3). I then changed the sheet name but
noticed in the immediate window that the name printed was still Sheet1.

Changing the line:
For Each sh In ThisWorkbook.Worksheets
to
For Each sh In Workbooks("MyWorkBookName").Worksheets
fixed the problem and now it works fine.

I have no other workbook open. Any ideas how this was happening?

Regards
Rowan


"Tom Ogilvy" wrote:

Change Col to point to the column you want to check. This assumes there
are
no blank cells in the data in the column.

Sub countunique()
Dim sh As Worksheet
Dim rng As Range
Dim sStr As String
Dim sForm As String
Dim col As Long
col = 2
For Each sh In ThisWorkbook.Worksheets
Set rng = sh.Range(sh.Cells(2, col), _
sh.Cells(Rows.Count, col).End(xlUp))
sStr = "'" & rng.Parent.Name & "'!" & _
rng.Address(0, 0, xlA1, False)
sForm = "SUM(1/COUNTIF(" & sStr & "," & _
sStr & "))"
num = Evaluate(sForm)
Debug.Print sh.Name, col, num
Next
End Sub

See the result in the immediate window of the VBE.

--
Regards,
Tom Ogilvy

"Giz" wrote in message
...
Hi, I have the relatively simple code below to conduct a count of
uniquely
identified records in excel spreadsheet:

count = 1
name = "simple_excel_export_table"
numrows = 19
For s = 2 To numrows
With Worksheets(name)
cellb = .Cells(s, 1).Value
cella = .Cells(s + 1, 1).Value
If cella cellb Then
count = count + 1
End If
End With
Next s

For example, this code on the following spreadsheet would result in a
count
of 4 (there are many more columns in my spreadsheet, this is just a
sample):

ID UserSiteID
1 61051
1 61051
1 61051
1 61051
1 61051
2 61050
2 61050
2 61050
3 61048
3 61048
3 61048
3 61048
3 61048
4 61043
4 61043
4 61043
4 61043
4 61043
4 61043

however, this code is to be run on multiple sheets with different
amounts
of
records from sheet to sheet. How can I enhance the code to assign the
proper
number of records, or rows, to the "numrows" variable for each
spreadsheet,
without having to manually go change the number assigned to "numrows"
each
time? Or is there a better way to do this?? any help appreciated