My auto fill VBA Code is slow.

[alz]

Sub insert()
Dim sheetname As String
sheetname = Sheet2.Name
If sheetname = ActiveSheet.Name Then
    Range("A1:A100000").EntireRow.Hidden = False
        Range("E100000").Select
        Selection.End(xlUp).Select
        Selection.End(xlUp).Select
        ActiveCell.Offset(0, -4).Range("A1:CZ3").Select
        Selection.AutoFill Destination:=ActiveCell.Range("A1:CZ6"), Type:=xlFillDefault
   ActiveCell.Offset(3, 0).Range( _
   "A2:A3,B2:B3,C2:C3,F2:F3,H2:M3,Q2:R3,X2,AA2,AD2,AG1:AZ3,BC2:CV3").Select
   Selection.ClearContents
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ActiveCell.Offset(-1, 0).Range("AG1:AZ1").Select
    Selection.FormulaR1C1 = "HAKA"
    ActiveCell.Offset(0, -28).Range("A1").Select
End If
  
End Sub

I used autofill to prevent breaking existing conditional formatting when adding new formated rows with formala.
Kindly advise. Thanks

 

[Joe4]

A few things:

1. You should never used reserved words (words of built-in Excel functions, properties, methods, etc) for the names of your procedures, functions, or variables. Doing so can cause errors or unexpected results. So you should NOT name your procedure "insert". I often preface the the names with "My", i.e. "MyInsert" to ensure that I won't run into that issue.

2. Instead of just posting your code, it would be helpful if we got a visual representation of that your data looks like, and what you would like your expected result to look like, along with a description of what exactly you are trying to do, as it relates to the data. I don't really like to make "assumptions", or guess what people are trying to do by trying to reverse engineer your code. Make it easy for people to help you!

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[alz]

A few things:

1. You should never used reserved words (words of built-in Excel functions, properties, methods, etc) for the names of your procedures, functions, or variables. Doing so can cause errors or unexpected results. So you should NOT name your procedure "insert". I often preface the the names with "My", i.e. "MyInsert" to ensure that I won't run into that issue.

2. Instead of just posting your code, it would be helpful if we got a visual representation of that your data looks like, and what you would like your expected result to look like, along with a description of what exactly you are trying to do, as it relates to the data. I don't really like to make "assumptions", or guess what people are trying to do by trying to reverse engineer your code. Make it easy for people to help you!

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi Joe4

Thanks for your valuable Tips. I am new to VBA . start learning from youtube video.
I upload my file on Dropbox.
File size is already big with formula and codes. I am figuring how can I reduce size and increase speed. Thanks

Transfer - Dropbox

Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!

www.dropbox.com

 

[Joe4]

I currently cannot download your file from my current location, but may be able to do so later today when I am at a different computer.
Hopefully, you provided the data to run this code against BEFORE the code has run, so we can run it ourselves and see it in action.

 

[alz]

I currently cannot download your file from my current location, but may be able to do so later today when I am at a different computer.
Hopefully, you provided the data to run this code against BEFORE the code has run, so we can run it ourselves and see it in action.

Hi @Joe4
Any advise?

 

[Joe4]

Here is one way to speed it up a little (note the lines in red):

Sub insertforEntry()
' insert Macro
' Keyboard Shortcut: Ctrl+Shift+I
' ActiveSheet.ShowAllData

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'formula to work only in entry sheet
Dim sheetname As String
sheetname = Sheet2.Name
If sheetname = ActiveSheet.Name Then
    Range("A1:A100000").EntireRow.Hidden = False
        Range("E100000").Select
        Selection.End(xlUp).Select
        Selection.End(xlUp).Select
        ActiveCell.Offset(0, -4).Range("A1:CZ3").Select
        Selection.AutoFill Destination:=ActiveCell.Range("A1:CZ6"), Type:=xlFillDefault
   ActiveCell.Offset(3, 0).Range( _
   "A2:A3,B2:B3,C2:C3,F2:F3,H2:M3,Q2:R3,X2,AA2,AD2,AG1:AZ3,BC2:CV3").Select
   Selection.ClearContents
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ActiveCell.Offset(-1, 0).Range("AG1:AZ1").Select
    Selection.FormulaR1C1 = "HAKA"
    ActiveCell.Offset(0, -28).Range("A1").Select
End If
   
  '  Range("_entry_f").Select
  '  Selection.Copy
  '  Selection.SpecialCells(xlCellTypeLastCell).Select
  '  Selection.End(xlToLeft).Select
  '  ActiveSheet.Paste
  '  Application.CutCopyMode = False
  '  ActiveCell.Offset(2, 5).Select
  '  Range("selection.END(xltoleft),activeCell.Offset(2, 5)").Select
  '  Cells(ActiveCell.Offset(0, -3), ActiveCell.Offset(2, 1)).Select

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

 

[alz]

Here is one way to speed it up a little (note the lines in red):

Sub insertforEntry()
' insert Macro
' Keyboard Shortcut: Ctrl+Shift+I
' ActiveSheet.ShowAllData

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'formula to work only in entry sheet
Dim sheetname As String
sheetname = Sheet2.Name
If sheetname = ActiveSheet.Name Then
    Range("A1:A100000").EntireRow.Hidden = False
        Range("E100000").Select
        Selection.End(xlUp).Select
        Selection.End(xlUp).Select
        ActiveCell.Offset(0, -4).Range("A1:CZ3").Select
        Selection.AutoFill Destination:=ActiveCell.Range("A1:CZ6"), Type:=xlFillDefault
   ActiveCell.Offset(3, 0).Range( _
   "A2:A3,B2:B3,C2:C3,F2:F3,H2:M3,Q2:R3,X2,AA2,AD2,AG1:AZ3,BC2:CV3").Select
   Selection.ClearContents
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ActiveCell.Offset(-1, 0).Range("AG1:AZ1").Select
    Selection.FormulaR1C1 = "HAKA"
    ActiveCell.Offset(0, -28).Range("A1").Select
End If
  
  '  Range("_entry_f").Select
  '  Selection.Copy
  '  Selection.SpecialCells(xlCellTypeLastCell).Select
  '  Selection.End(xlToLeft).Select
  '  ActiveSheet.Paste
  '  Application.CutCopyMode = False
  '  ActiveCell.Offset(2, 5).Select
  '  Range("selection.END(xltoleft),activeCell.Offset(2, 5)").Select
  '  Cells(ActiveCell.Offset(0, -3), ActiveCell.Offset(2, 1)).Select

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Thanks For Your advise!

 

[Joe4]

You are welcome.