I can see the file.
Do you expect miracles? Didn't it take two hours on yours to run? I haven't got a Magic Wand!
Please wait... analysing data.
0% Complete
_______________________________________________
I can see the file.
Do you expect miracles? Didn't it take two hours on yours to run? I haven't got a Magic Wand!
Please wait... analysing data.
0% Complete
_______________________________________________
Semper in excretia sumus; solum profundum variat.
Several reasons that I can see for it being a large file and taking forever to load and calculate
I never bothered to let it calculate since it takes so l-o--o-n-g
1. There must be 10s of 1,000s of empty cells that have a formula in them that requires recalculating if any dependent data changes
2. There must be dozens of completely data-less sheets, filled with formulas
3. There is a lot of formatting in cells that takes space
4. Your worksheets go almost 20 years into the future with formulas, but no raw data into that time period
5. The XLSM format is a compressed ZIP under the hood - Just the main Excel unpacked (so Excel can get to it) is 216 MB, with just the worksheets using 196MB. So I'm guessing that there's a lot of paging going on
6. Your CalcChain.xml is almost 17 MB unpacked (I think it controls the order of cell calculation).
7. Some formulas are very complex and seem to require dependent data, forcing a re-calc. Also, many cells have that formula, but the cells that it uses are blank
See #64
Capture.JPG
Capture.JPG
Capture.JPG
Capture1.JPG
Capture.JPG
Last edited by Paul_Hossler; 05-21-2020 at 08:34 AM.
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Well explained Paul H, but I think you are being a bit kind there! I can only see two ways of dealing with that workbook, 1. Start again with a completely new design philosophy or 2. Pop down to PC World and purchase a new Acer 64QuBit Laptop.
Steve, this is the code:
It takes an age to run (every line it deletes takes just under 2 seconds!)Sub DelPOs() Dim arS1 As Variant, arS2 As Variant, lr As Long, i As Long, j As Long, sh As Worksheet Dim dic As New Scripting.Dictionary, ky As Variant, tm# Dim rws As String, arRws As Variant Application.ScreenUpdating = False Application.Calculation = xlCalculationManual tm = Timer 'Get list of PO's to search for lr = Sheets("Accrual & PO Data").Cells(Rows.Count, 1).End(3).Row arS1 = Sheets("Accrual & PO Data").Range("A1:A" & lr) For i = 2 To UBound(arS1) If Not dic.Exists(arS1(i, 1)) Then dic.Add arS1(i, 1), Nothing Next uf1.Show 'Loop through sheets For Each sh In ThisWorkbook.Worksheets 'Don't include the following sheets If sh.Name <> "Instructions" And _ sh.Name <> "Accrual & PO Data" And _ sh.Name <> "Tab Name List" And _ sh.Name <> "Subtotal Macro Button" And _ sh.Name <> "Input Date" And _ sh.Name <> "Summary FY19 F1(5)" And _ sh.Name <> "Summary FY19 F1(4)" And _ sh.Name <> "Summary FY19 F1(3)" And _ sh.Name <> "Summary FY19 F1(2)" And _ sh.Name <> "Summary FY19 F1" And _ sh.Name <> "EP Local" And _ sh.Name <> "Driver Definitions" And _ sh.Name <> "EP Global" Then uf1.Label1 = sh.CodeName & " (" & sh.Name & ")" DoEvents 'Get list of PO's on current sheet lr = sh.Cells(Rows.Count, 1).End(3).Row If lr < 3 Then lr = 3 'There are blank sheets! arS2 = sh.Range("A1:A" & lr) 'Loop through search PO's rws = "" For Each ky In dic.Keys 'Loop through sheet PO's For j = UBound(arS2) To 3 Step -1 'If there is a PO match, delete row If ky = arS2(j, 1) Then Worksheets(sh.Name).Rows(j).EntireRow.Delete shift:=xlUp Next Next End If Next Unload uf1 MsgBox Timer - tm & " seconds to complete." End Sub
When the code has finished, the workbook wheezes for about 20 minutes trying to get over the shock of something worthwhile happening to it!
I put a userform in there which lets you know which sheet it is working on and the fact that it hasn't gone to sleep.
Seriously, re-think that workbook and its' pitfalls and then start again with fresh views. There are loads of people here willing to point you in the right direction, just ask... and, more importantly, take in the advice they give, it's priceless.
Attached is a slimmed down version of your file (I ran a script to delete all the empty rows on each sheet) with the uf in it.
Semper in excretia sumus; solum profundum variat.
1. Define the references on a sheet, small DB so that you can take decode fields into 'people-speak'. e.g.
Account = 1000 and see user = Bill
Account = 2000 and see user = Tom
2. Start with raw / unprocessed data either on one sheet or by using macro to pull it in from other sources / workbooks
3. Macro to process raw data sheet by ...
deleting rows or columns
formatting
adding new columns using decodes above
calculating any fields that will make reporting easier as VALUES, e.g. take PO date (5/1/2020) and store FY (05-2020) in a new column
Use SUM, SUMIF, COUNTIF, etc. as Application.WorksheetFunction if needed (intrinsic functions are faster than a VBA loop)
4. Use pivot tables to make analysis easier
5. Use macros to generate highly formatted 'reports' in separate workbooks, or l like to generate a report in a worksheet in the main book, but save it as a PDF and then delete the temporary worksheet from the main book
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3