I'm trying to build an array where the start of each
element will be defined by a particular value in
Col 6.

In the following code I receive the above error message
on the line marked ***

For Each ws In Workbooks(SourceWorkbook).Worksheets()
With ws
Shtname = ws.Name
Dim i As Long
Dim varr()
Dim rng As Range, Cell As Range, rng1 As Range
ReDim varr(1 To 1)

If Not (UCase(.Name) = "MASTER" Or UCase(.Name) _
= "COVER" Or UCase(.Name) = "CONTENTS" Or _
UCase(.Name) = "SUMMARY") Then
*** Set rng = Columns(6).SpecialCells(xlConstants, xlTextValues)
For Each Cell In rng
If Cell.Value = "£" Then
If IsEmpty(varr(1)) Then
Set varr(1) = Cell
Else
ReDim Preserve varr(1 To UBound(varr) + 1)
Set varr(UBound(varr)) = Cell
End If
End If
Next
etc

As far as I can see with the test workbook, there are instances of
'£' in Col 6 in every worksheet. Why does the code appear to fail to
find '£', please?

Regards.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003