0

I am a newbi to excel VBA. I created a macro to insert a row to the top of a table and it will also copy the font and formulas from the row below. The formulas are such, they import data from another excel file. But now i have a problem where it is getting very slow and it is flickering etc. I looked on the internet to optimize my VBA code but i couldn't really find something helpfull.

This is my code, how can i optimize this:

Sub Add_row()
'Add_row Macro

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False

    Range("A4:R4").Select
    Selection.ListObject.ListRows.Add (1)
    Range("A5:A8").Select
    Range("A8").Activate
    Selection.AutoFill Destination:=Range("A4:A8"), Type:=xlFillDefault
    Range("A4:A8").Select
    Range("B5:I5").Select
    Selection.AutoFill Destination:=Range("B4:I5"), Type:=xlFillDefault
    Range("B5:I5").Select
    Range("J5:R5").Select
    Selection.AutoFill Destination:=Range("J4:R5"), Type:=xlFillDefault
    Range("J5:R5").Select
    ActiveWindow.SmallScroll Down:=-2

    'Application.Calculation = xlCalculationAutomatic

    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True

End Sub

Thank you

Rik Sportel
  • 2,661
  • 1
  • 14
  • 24
laaww
  • 1
  • 1
    I don't understand why you need anything beyond `Range("A4:R4").ListObject.ListRows.Add Position:=1`. That should insert a new row at the top of the table and populate all the formulas while leaving any typed constants blank. –  Jun 10 '18 at 15:50
  • Have a look at [how to avoid select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Rik Sportel Jun 10 '18 at 17:41
  • Hello Jeeped thank you for your response. i tried: Range("A4:R4").ListObject.ListRows.Add Position:=1 – laaww Jun 13 '18 at 14:07
  • it adds a row on top but then it doesn't copy all the formulas from the row below. If i could attach a picture in here then you will be able to see it more clearly – laaww Jun 13 '18 at 14:20

0 Answers0