How to dynamically get Active cell address and find out range in EXCEL VBA macro

Question

In a Excel Macro, I want to achieve this: In 'A' column there can be data in a number of rows. When user selects any Cell in 'A' col, and clicks a button, then 6 rows get inserted below selected row and data from the selected cell gets copied over to those 6 rows. I recorded a macro, and here is what I have:

Sub MacroTest()
'
' MacroTest Macro
'

    Rows("5:5").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A4").Select
    Selection.AutoFill Destination:=Range("A4:A10"), Type:=xlFillDefault
End Sub

What I would like to know is how to replace A4 with Active cell, and the range A4:A10 and Active cell address + 6. Please advise!

Marcucciboy2 · Answer 1 · 2018-08-06T15:33:10.867

3

Here's my take on it. This way you can easily adjust the number of cells that you want to copy after the activecell if you change your mind, and it gets rid of that verbose code :)

Sub MacroTest()

    If ActiveCell.Column = 1 Then

        Dim numCopies As Long
        numCopies = 6

        Dim i As Long
        For i = 1 To numCopies
            Rows(ActiveCell.row + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Next i

        ActiveCell.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(numCopies, 0)), Type:=xlFillDefault

    End If

End Sub

edited Aug 06 '18 at 15:33

answered Aug 06 '18 at 15:17

Marcucciboy2

3,156
3
20
38

1

Probably declare `Dim i As Long` and use `Option Explicit` – Pᴇʜ Aug 06 '18 at 15:20
works great! How do we make sure that ActiveCell is in Column 'A' only? – SilverFish Aug 06 '18 at 15:29
@Marcucciboy2, One more minor issue - The spreadsheet has color formatting from Column A thru E. Now when I run macro, only col A formatting is working for new rows created and the rest of the columns are messed up – SilverFish Aug 06 '18 at 18:39
@SilverFish that's probably because we're only doing the autofill for the first column – Marcucciboy2 Aug 06 '18 at 18:41
@Marcucciboy2, I understand that. Isn't there a way to copy/paste data of first col, but copy/paste formatting of entire row or Col A - Col E? or have another For /Next loop? – SilverFish Aug 06 '18 at 18:44
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/177513/discussion-between-marcucciboy2-and-silverfish). – Marcucciboy2 Aug 06 '18 at 18:46

score 2 · Answer 2 · answered Aug 06 '18 at 15:11

Recording a macro is one of the best ways to start programming in vba, congrats. This is how to do the ActiveCell and the Offset():

Sub MacroTest()
    Rows("5:5").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    'Range("A4").Select
    ActiveCell.Select
    Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(6, 0)), Type:=xlFillDefault
End Sub

If you want to have the code improved a bit, consider putting it in https://codereview.stackexchange.com, some good ideas would pop up.

E.g., not using Selecti in Excel - How to avoid using Select in Excel VBA.

How to dynamically get Active cell address and find out range in EXCEL VBA macro

2 Answers2