Stopping the "Next" command when a blank cell is reached.

[Guest]

Hi all,

I have the following that copies files from folders:-

----------------------
Sub copysomefiles()
On Error GoTo ErrorHandler
Dim sMess As String
Dim ans
sMess = "Have you entered your File Prefix (if required) and Destination
Filepath (3 & 4)?" & vbCrLf & vbCrLf & _
"Press Yes to confirm, or No to cancel"
ans = MsgBox(sMess, vbYesNo, "Copy Filenames")

If ans = vbYes Then
Dim fname As Range
For Each fname In Range("fileRange")
FileCopy Range("source") & fname, Range("destination") & Left(fname, 8) &
Range("prefix") & Right(fname, 4)

Next

Exit Sub
ErrorHandler:
If ans = vbNo Then Exit Sub
End If
End Sub
----------------

The range "fileRange" is basically the whole of column A, but there can be
any number of cells with data in them, and the rest of the range being blank
(i.e A1:A100 with data and A100:A65536 without data).

How can I get the next command to stop when its reaches a blank cell (i.e.
cell A101)?


Thanks,

Bhupinder

 

[Guest]

This is one simple way:


For Each fname In Range("fileRange")
If fname="" then exit for
FileCopy Range("source") & fname, Range("destination") & Left(fname, 8) &
Range("prefix") & Right(fname, 4)

Next

 

[Guest]

toppers,

thanks for the help, that works great!

Regards,

Bhupinder