You can test for empty in the loop:

Sub PasteData()

Dim r As Range

For Each r In Worksheets("Makron").Range("B6:B14")
If IsEmpty(r.Value) Then
Exit For
End If
GetPortfolioData Portfolio:=r.Value
Next

End Sub
--
Gary''s Student


"sverre" wrote:

> Hi
> How do I instead of selecting the range B6:Bb14 below tell excel to loop
> starting from b6 and continue down till the cell in col B is empty?
>
> ?Sub PasteData()
>
> Dim r As Range
>
> For Each r In Worksheets("Makron").Range("B6:B14")
> GetPortfolioData Portfolio:=r.Value
> Next
>
> End Sub