My auto fill VBA Code is slow.

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
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

 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
Here is one way to speed it up a little (note the lines in red):
Rich (BB code):
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
 
  • Like
Reactions: alz

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
Here is one way to speed it up a little (note the lines in red):
Rich (BB code):
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,475
Messages
5,636,550
Members
416,923
Latest member
jarri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top