Back to the posting the workbook issue! You need to set the reference to the Microsoft Scripting Runtime.
Back to the posting the workbook issue! You need to set the reference to the Microsoft Scripting Runtime.
Semper in excretia sumus; solum profundum variat.
Darn. I am not sure what that means?
ok. I found that setting. Now I get this. See the attachment.
The Data I want to search / reference is in column A of the "Accrual & PO Data" worksheet. So I tried to change your code to Sheet 2. The highlighted yellow in the thumbnail.
That did not work. What am I doing wrong?
Thanks a ton for your expertise! I think that you have me so close to solving my need.
Steve
From post #39It compiled but I haven't tested it because the sheet numbers have changed, that code refers to Sheet2 which doesn't exist in this workbook.
Then on the next line you've changed that to Sheet1!!
They need to be changed to either the correct sheet number for the PO sheet OR changed to:
etclr = Sheets("Accrual & PO Data").Cells...
Semper in excretia sumus; solum profundum variat.
In the last workbook you sent me, the sheet was Sheet153
IF they are the first 13 Sheets in the workbook then you can use
lr = Sheet153.Cells(Rows.Count, 1).End(3).Row arS1 = Sheet153.Range("A1:A" & lr) 'Loop through sheets For Each sh In ThisWorkbook.Worksheets 'Don't include 1st 13 sheets If sh.Index > 13 Then 'Get list of PO's on current sheet
Last edited by paulked; 05-14-2020 at 03:58 AM.
Semper in excretia sumus; solum profundum variat.
Paulked,
OK. I will try this tonight. My intention was to skip the first 13 worksheets and not run your code on those. But, the "PO Accrual" worksheet is still needed as the reference to compare all the other worksheets against the PO # column A and then delete out of that worksheet any matches.
I am not sure what you mean about sheet 153? That was the last worksheet in that big file. I need to look at all worksheets (except the first 13) and then compare them to column A in the "PO Accrual" tab.
Will your additional code above accomplish this?
Thanks again for all your help.
Steve
Sheets can be addressed by either their sheet name (tab name) or their code name.
I normally address them by their code name as the sheet name can easily be changed by the user (as you did with "PO Accrual Data" in post #24 to "Accrual & PO Data" in post #28).
The code name is the name in the Project Explorer before the brackets, the sheet name is the name you see in brackets in the VBE or on the tab of the sheet in Excel:
67341b.png
The Sheet153 code in post #46 can be replaced by either the code name for the PO Accrual sheet or Sheets("Accrual & PO Data") if it is still named that.
IF the first 13 tabs in the workbook are the ones you want to skip, then the code in #46 If sh.Index > 13 Then can be used, otherwise you need to list the sheets you want to skip.
Semper in excretia sumus; solum profundum variat.
Since you can't trust users to leave things alone, I'd suggest giving the sheets a meaningful Code Name and using that to determine which sheets to skipIF the first 13 tabs in the workbook are the ones you want to skip, then the code in #46 If sh.Index > 13 Then can be used, otherwise you need to list the sheets you want to skip.
Capture.JPG
---------------------------------------------------------------------------------------------------------------------
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
Thanks Paul, good suggestion, but I doubt that will happen I'm almost through this wall though
Semper in excretia sumus; solum profundum variat.
Paulked,
I have attached the document with the VBA code. I believe I have included all of your suggestions and VBA code. It is now getting hung up on "Dim dic As New Scripting.Dictionary, kys() As Variant, ky As Variant, tm#".
Why would it get hung up on a Dim?
Thanks.
Steve
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, kys() As Variant, ky As Variant, tm# tm = Timer 'Get list of PO's to search for lr = Sheet("Accrual & PO Data").Cells(Rows.Count, 1).End(3).Row arS1 = Sheet("Accrual & PO Data").Range("A1:A" & lr) 'Loop through sheets For Each sh In ThisWorkbook.Worksheets 'Don't include PO Accrual Data 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 '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 For i = 2 To UBound(arS1) 'Loop through sheet PO's For j = 3 To UBound(arS2) 'If there is a PO match, add it to the dictionary if not already in there If arS1(i, 1) = arS2(j, 1) Then If Not dic.Exists(arS2(j, 1)) Then dic.Add arS2(j, 1), Nothing End If Next Next End If Next 'Loop through list to delete For i = UBound(arS1) To 2 Step -1 'Loop through dictionary items For Each ky In dic.Keys 'If there is a match. delete the PO row If Sheet("Accrual & PO Data").Cells(i, 1) = ky Then Sheet1.Rows(i).Delete shift:=xlUp Next Next 'Show deleted PO's kys = dic.Keys If dic.Count <> 0 Then 'Can't print nothing! Sheet1.Range("E5").Resize(dic.Count) = Application.Transpose(kys) End If Sheet1.Range("E" & dic.Count + 6) = Timer - tm & " seconds to complete." End Sub
It is probably unable to compile, not hang, because you haven't set reference to the Scripting Runtime.
Semper in excretia sumus; solum profundum variat.
How do I set a reference? I have never used Scripting Runtime before.
Thanks.
Steve
You have! See my post #41and then read your post #43You need to set the reference to the Microsoft Scripting Runtime.Anyway, here it is on a nice silver spoon, fresh from Google:ok. I found that setting.
How do I enable Microsoft Scripting Runtime?
Setting the Reference to the Microsoft Scripting Runtime Library
- In the VB Editor, click on Tools.
- Click on References.
- In the References dialog box that opens, scroll through the available references and check the 'Microsoft Scripting Runtime' option.
- Click OK.
Semper in excretia sumus; solum profundum variat.
Paulked,
It worked! One more question. What do I add to the code if I want to delete PO rows if there are multiple instance of that same number in the "PO & Accrual Data" worksheet. For example, there may be more than one in column A such as:
239618 239618 239618 239618 239618
Paulked,
I run it an I do not get any breaks or error messages. However, it does not delete the rows in the "PO & Accrual Data" worksheet. But, you have the code here. Not sure why it is not executing that step? And it is a loop so it should be deleting all instances in that worksheet.
Any ideas?
I have attached the document.
'Loop through list to delete For i = UBound(arS1) To 2 Step -1 'Loop through dictionary items For Each ky In dic.Keys 'If there is a match. delete the PO row If Sheet153.Cells(i, 1) = ky Then Sheet278.Rows(i).Delete shift:=xlUp Next Next 'Show deleted PO's kys = dic.Keys If dic.Count <> 0 Then 'Can't print nothing! Sheet278.Range("E5").Resize(dic.Count) = Application.Transpose(kys) End If
Paulked,
Here is the attachment that I forgot to add to the last thread.
Thanks.
Steve
Semper in excretia sumus; solum profundum variat.
Paulked,
Ah! That was it, I had the wrong Sheet#! I now put this code into my 35M workbook. It is running, but taking a long time to run. The workbook has a lot of nested formulas which is what probably makes it big. Is there a way to speed it up? May put in Application.Calculation = xlCalculationManual? If so, would I put it in the loop or maybe at the top of the program?
Thanks for all of your expertise help! How did you learn VBA? I have learned a lot from you. But wondering how you became an expert.
Steve
I'm no expert, but what I read I tend to retain.
If you are sure it is set to automatic before the code runs then set it to manual at the beginning, then set it back to automatic at the end, don't do it in the loop, it will slow things down even more!!!!!!!!!!!!!
Semper in excretia sumus; solum profundum variat.