Autofill from a named range to a unnamed destination in Excel using VBA

I am trying to autofill from a named range to its right neighbour cells. The original range contains combined cells and is 4x4 cells in size. My problem is that the Autofill-method needs a Destination:=Range("...") and does not take something like Destination:=xlSameAreaToTheRight.

The solution would be if I could do something like

Range("origin").AutoFill Destination:=( Range("origin").Area + Rows(4) ), _
                         Type:=xlFillDefault

So how can I find the rows and cols of a n开发者_如何学Camed range in the format A1:D4 and add like 4 cols to it?

Thnks for your support!

I am not sure what you want to get as result, but here are the answers to your particular questions:

You can get the Range as String in the format $A$1:$D$4 using .Address, and to get the amount ofthe columns just add .Columns.Count to the named range. (Using offset is should be no problem to add the amount of the columns to the active cell within your range.)

Dim TestRange As String
Dim AmountColumns As Integer

TestRange = ActiveWorkbook.Sheets(1).Range("Test").Address
AmountColumns = ActiveWorkbook.Sheets(1).Range("Test").Columns.Count

Thanks to stema's answer I found a (quite simple) solution:

To add rows or cols to a named range and use it as AutoFill destination one can simply do

Range("origin").AutoFill Destination:=( Range("origin").Range("A1:H2") ), _
                         Type:=xlFillDefault

what would autofill four columns and two rows to our "origin":

|       1       |       2       |
|  1,1  |  1,2  |  2,1  |  2,2  |

This leads to:

|       1       |       2       |       3       |       4       |
|  1,1  |  1,2  |  2,1  |  2,2  |  2,7  |  3,12 |  3,54 |  3,96 |

Here's one way to use @stema's suggestion.

Sub namedfill()

    Dim locOrig As String
    Dim firstCell As String
    Dim lastCell As String

    'Pluck address of top-left and bottom-right cells from .Address string
    locOrig = Range("Origin").Address
    firstCell = Left(locOrig, 4)
    lastCell = Right(locOrig, 4)

    Range("Origin").AutoFill Destination:=Range(firstCell, Range(lastCell).Offset(0, 4)), Type:=xlFillDefault

End Sub