View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.setup
Mark
 
Posts: n/a
Default Copy/Paste using a macro that identifies occuoied range

I am not sure if you know how to use custom functions, but here is one
that will return the first range that is blank.
You must call the function by passing it the range you want to start
from, in your case, Range("B9"). consider the following example:

Sub test()

Dim Myrng As Range

Set Myrng = FindBlank(Range("B9"))
MsgBox "First Blank found @ " & Myrng.Address

End Sub

if you run this macro then the Message Box will display the Address of
the first cell found that is blank.

Here is the Function.

Function FindBlank(CurRng As Range) As Range

Dim TempRng As Range
Set TempRng = CurRng
Do
Debug.Print TempRng.Address
If TempRng < "" Then
Set FindBlank = TempRng.Offset(4, 0)
'Debug.Print "Checking next cell @ " & FindBlank.Address
Set TempRng = FindBlank
Else
FindBlank = TempRng
Exit Function
End If
Loop

End Function

If you have trouble give me a holler.