Merge two pieces of code into one

[Steph]

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"), 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

 

[BrianB]

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.

 

[Steph]

Hi Brian. Exactly! Now the real question is how?! I don't mean to
sound like a dope, but I'm a bit of a dope when it comes to VBA!

 

[BrianB]

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