Autofill in a variable range Closed

Question

Hello, I'm working on a macro in which I have to add a row or a column, and they can be inserted anywhere in my sheet. I managed to add the rows/columns, but I also need to extend the formulas to the new ones. 

I tried using the Autofill method, but I have problems defining source/destination range.
I tried to define a variable for the range, but I get an error (I was making it up... I kind of expected it to fail... I did it based on a recorded macro) Can you help me how to work with variable ranges?

This is what I tried to do.


Dim NRow as Integer 'Row number
Dim strRow1 as String 'Limit 1 of my range
Dim strRow2 as String 'Limit 2 of my range

'I want to add the row according to the selected cell        
      ActiveCell.EntireRow.Select

'To get the row of my selection
      nRow=Selection.Row

'Add line
      Selection.EntireRow.Insert Shift:=xlDown

'Define the range of my rows
        strRow1 = NRow & ":" & NRow
        strRow2 = NRow - 1 & ":" & NRow
       
        Rows(strRow).Select
        Selection.AutoFill Destination:=Rows(strRow2), Type:=xlFillDefault


'My recorded macro did something like this..... that's why I was trying to build the argument of the Range..... 
        'Rows("14:14").Select
        'Selection.AutoFill Destination:=Rows("13:14"), Type:=xlFillDefault


Thank you very much!!


Configuration: Windows 7 / Safari 535.2

venkat1926 · Answer

when you are learning macro from recording macro you must introuduce number of message boxes (msgsbox) so that you know what you are doing and correct it if wrong 

one more thing there is a line
Rows(strRow).Select
what is stsrRow . there is no mention. Is it a typo
correct it if necessary

now dubug the macro below. how 
reduce the size of spreadsheet and vbeditor (macro area) by clicking the center icon on the top right left to x icon and keep the two side by side 
now go to macro and keep the cursor within some where in the macro and successively hit F8.

you will find some descrepancy and correct it.

Sub test()
Dim NRow As Integer 'Row number
Dim strRow1 As String 'Limit 1 of my range
Dim strRow2 As String 'Limit 2 of my range

'I want to add the row according to the selected cell
      ActiveCell.EntireRow.Select
MsgBox ActiveCell.Row
'To get the row of my selection
      NRow = Selection.Row
MsgBox NRow
'Add line
      Selection.EntireRow.Insert Shift:=xlDown

'Define the range of my rows
        strRow1 = NRow & ":" & NRow
        strRow2 = NRow - 1 & ":" & NRow
       MsgBox strRow1
       MsgBox strRow2
       
        Rows(strRow).Select
        MsgBox Selection.Address
        Selection.AutoFill Destination:=Rows(strRow2), Type:=xlFillDefault



End Sub

Autofill in a variable range

1 response

Similar discussions

Subscribe To Our Newsletter!