Auto Fill from multiple selected cells VBA

[dimitri]

Hi,
I have written a short piece of code that identifies a target cell, inserts a row above it, then moves two cells up from the target cell. It then selects the current active cell and the three cells to the right and autofills down two rows.

The code does exactly what I want it to do, but I have 2 questions:

1) I don't understand why the last part of the second line of the code (Range("A1:D1").select) selects the two cells to the right of the active cell. It seems like it should be selecting the actual range A1:D1? I used the macro recorder to get this part of the code which is why I don't understant it. Again, it does exactly what I want, I'm just confused as to how.

2) Is there a more effecient way to write this code. I generally try to stay away from selecting cells but this is the only way I could figure out how to do it.

The code is below.

Range("Target.Cell").EntireRow.Insert
Range("Target.Cell").Offset(-2, 0).Range("A1:D1").Select
Selection.AutoFill Destination:=Range(Selection, Selection.Offset(2, 0))

Any input would be appreciated. Thank you.

 

[ukmikeb]

Hi

Try -

Range("Target.Cell").EntireRow.Insert
Range("Target.Cell").Offset(-2).Resize(1, 4).Select
Selection.AutoFill Destination:=Selection.Resize(3, 4)

1, is a mystery and is probably due to the VBA compiler anticipating what it is receiveing rather than flagging an error.

2, You could do away with the Select but you would have to replace "Selection" with "Range("Target.Cell").Offset(-2).Resize(1, 4)" before the ".Autofill" and "Range("Target.Cell").Offset(-2)" after "Destination:=".
Selection seems a whole lot cleaner.

hth

 

[dimitri]

"Resize" makes much more sense. I think I'll stick with the selection bit as well, the code is short enough not to slow things down.Thanks for the help.

 

[mirabeau]

Hi dimitri,

A comment on your first point.

If you run the following code

Range("C3").Range("A1:D1").Select
MsgBox "note selection"
Cells(5, 2).Range("A1:D1").Select
MsgBox "note selection"
Cells.Range("A1:D1").Select

and note what is selected, it should indicate your answer.

What this code is doing is defining a starting reference point for the range of interest, this being cell Range("C3"), instead of the default Range("A1") which occurs on a new worksheet (unless you've changed the starting defaults).

This is just how the VBA was written. It's consistent with other uses of Excel worksheet ranges in VBA. What comes before the period, or fullstop, defines the starting point of whatever resizing mechanism is used to determine the subsequent (i.e. after the period) range.

 

[ukmikeb]

dmitri

Pleased to have helped you solve your problem.

Thanks for the feedback.

Good luck with your project


@mirabeau Yes, it looks as if the second Range is mirroring a Resize.