Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge two pieces of code into one
Hi. Thanks to all of you, I know have 2 pieces of code that (at least
thoretically) do exactly what I need done. The problem is with the size of the files, and available memory. I ran the code on my boss's dual-processor workstation, and it still crashed. Code 1 opens up several text files and pastes them into a separate worksheet within 1 workbook. Each sheet has approx 40k rows. Code 2 deletes the lines on each sheet that do not contain the words 'login' or 'logoff' or 'timeout'. So that being said, maybe the code will run faster if I combine the 2 modules into one loop. So instead of opening ALL files and pasting them into one workbook, and THEN deleting unnecessary rows, can we bring in one file, copy that one file into a worksheet, delete the unnecesary rows, and then bring in the second file.....etc. This way the workbook never gets too huge. Can you help me combine the 2 pieces of code? They are below: Code 1 Sub Combine2() Dim GetFiles As Variant Dim iFiles As Long Dim nFiles As Long Dim wkbk As Workbook GetFiles = Application.GetOpenFilename _ (FileFilter:="Text Files (*.*),*.*", _ Title:="Select Files To Open", MultiSelect:=True) If TypeName(GetFiles) = "Boolean" Then ''' GetFiles is False if GetOpenFileName is Canceled MsgBox "No Files Selected", vbOKOnly, "Nothing Selected" End Else ''' GetFiles is Array of Strings (File Names) ''' File names include Path For iFiles = LBound(GetFiles) To UBound(GetFiles) Workbooks.OpenText fileName:=GetFiles(iFiles) Set wkbk = ActiveWorkbook wkbk.ActiveSheet.Copy After:=ThisWorkbook.Worksheets(1) 'Note: Thisworkbook refers to the workbook the macro is runningfrom wkbk.Close Next End If End Sub Code 2 Sub DelRowsFinal() Dim x As Long Dim c As Range Application.ScreenUpdating = False For x = ActiveSheet.UsedRange.Rows.Count To 1 Step -1 With Intersect(Range("D:D"), ActiveSheet.UsedRange.Rows(x)) Set c = .Find("logoff", LookIn:=xlValues) If c Is Nothing Then Set c = .Find("timeout", LookIn:=xlValues) If c Is Nothing Then Set c = .Find("logon", LookIn:=xlValues) If c Is Nothing Then .EntireRow.Delete End If End If End If End With Next x Application.ScreenUpdating = True End Sub Thank you!!! Steph |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge two pieces of code into one
METHOD
So, logically, to keep the memory usage to a minimum, the process should be :- 1. Open File 2. Delete rows as necessary. 3. Transfer remainder to main file (copy/paste is quicker than row by row.) 4. Close File 5. Loop back to 1. TIME SAVING 1. Application.Calculation = xlCalculationManual at the beginning. Back to xlCalculationAutomatic at the end. 2. Limit the Find process to a single column instead of searching the whole sheet. 3. I would try sorting the data first and doing a block row deletion. 4. Use Copy/Paste to transfer to the main file. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge two pieces of code into one
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge two pieces of code into one
This is not a straightforward issue - and we generally get paid fo
writing code <<grin. You are "starting from the wrong place". Generally it is best to star the macro recorder and record your actions. This gets things started a well as clarifying the method. The method I suggest is :- 1. sort the data 2. find the first line to delete 3. delete the rows. Having done this, the next job will be to change the code so it wil work on any sheet, and then get it to delete the other items. I suggest you make a new message for further problems because many o us do not tend to read messages with replies already there -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merge base on same code number | Excel Worksheet Functions | |||
First zero doesn't show up in zip code mail merge. | Excel Worksheet Functions | |||
zip code with mail merge | Excel Discussion (Misc queries) | |||
Code launches Mail Merge but disables the Mail Merge | Excel Discussion (Misc queries) | |||
How do I input zeroes in a zip code for a mail merge? | Excel Worksheet Functions |