View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Mike R. Mike R. is offline
external usenet poster
 
Posts: 59
Default Filling Array from Cells on Worksheet

these both worked perfect....thank you!

"OJ" wrote:

Hi,
Try this...
Sub trythis()
Dim myArr() As String, rngRecip As Range, rngCell As Range, intInc As
Integer

Set rngRecip = ThisWorkbook.Sheets(1).Range("$A$1:$A$6")

For Each rngCell In rngRecip
With rngCell
If .Value < "" Then
ReDim Preserve myArr(intInc)
Let myArr(intInc) = CStr(.Value)
End If
End With
Next rngCell

With ThisWorkbook
.HasRoutingSlip = True
.RoutingSlip.Recipients = myArr
.Route
End With

Set rngRecip = Nothing
End Sub