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.
|