Replacing a "Do... Loop Until" routine with something a littlef aster
I have a code that goes back and forth between two workbooks in order to identify matches between them and hide the rows for which there is no match. It works very well, with no screen flicker and no visible moving back and forth between books, and it doesn't take all that long, about a minute and a half; but it's always nice to dazzle whenever possible :-)
Is there a way to do this faster? Given the fact that it has to go back and forth between the two books?
Thanks,
John
Sub FindMatches()Application.ScreenUpdating = FalseDoActiveWindow.ActivateNextDim str As Stringstr = ActiveCell.ValueActiveCell.CopyActiveCell.Offset(1, 0).ActivateActiveWindow.ActivateNext Cells.Find(What:=str, After:=ActiveCell, LookIn:=xlValues, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).ActivateActiveCell.EntireRow.Interior.ColorIndex = 45ActiveCell.Offset(0, -1) = 1Dim top As Range, btm As RangeSet top = [C6]Set btm = ActiveCellRange(top, btm).SelectFor Each cel In Range(top, btm) If cel.EntireRow.Interior.ColorIndex <> 45 Then cel.EntireRow.Hidden = True Else End IfNextbtm.Offset(1, 0).ActivateLoop Until IsEmpty(ActiveCell)[Counts].Calculate[C2].ActivateApplication.ScreenUpdating = TrueEnd Sub
gabrielPennybackReliability Business Tools Analyst IIAuthor Commented:
Hi Sid, thanks. The calling workbook is named "SDAE_1.xls" and the the other workbook is named "KRG_1.xls"
Thanks for pointing out line 19. I just had it in there for testing. I always do that, and often forget to remove it.
Yes please let me know how to use the stored names of the workbooks in the code, and of course how to do the storing in the first place!
And I'll really be interested to see if there's a way to go through the cells in both books without activating.
It would be good to see your books/code so we could tailor a response to this
as an example you can kill this entire For Loop section (the snippet below) - the usual culprit in slow code - by turning the logic on its head. instead
a) hiding all the rows in this range
b) finding all cells with a colorindex of 45 using Find (there is a Format option)
c) unhide these rows
cheers
dave
0
Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Here is a basic Idea on how to go about it. I have commented some code as I am unsure as to what you want to do.
Sub FindMatches() Dim wb1 As Workbook, wb2 As Workbook Dim str As String Dim aCell As Range Dim top As Range, btm As Range Application.ScreenUpdating = False '~~> SDAE_1.xls Set wb1 = ActiveWorkbook lastrow = wb1.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row Set wb2 = Workbooks.Open("C:\KRG_1.xls") '~~> Looks for text from Col A of wb1 in wb2 For I = 1 To lastrow str =wb1.Sheets("Sheet1").Range("A" & i).Value Set aCell = wb2.Sheets(1).Cells.Find(What:=str, After:=ActiveCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate If Not aCell Is Nothing Then aCell.EntireRow.Interior.ColorIndex = 45 aCell.Offset(0, -1) = 1 'Set top = [C6] 'Set btm = ActiveCell 'For Each cel In Range(top, btm) 'If cel.EntireRow.Interior.ColorIndex <> 45 Then 'cel.EntireRow.Hidden = True 'Else 'End If 'Next 'btm.Offset(1, 0).Activate Next I '[Counts].Calculate '[C2].Activate Application.ScreenUpdating = TrueEnd Sub
Please note that the code that I gave you is just a general representation of your code. You might have edit paths, sheetnames, cell references etc. Also please note that you don't have to keep the other workbook open. The code will open it for you. Once your task is over, you may close the 2nd workbook by introducing the code
wb2.close (True)
True is to close the workbook with changes saved. If you want to ignores changes then replace true by false.
ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.
One of a set of tools we're offering as a way to say thank you for being a part of the community.
1) Yes. Instead of using Active Window, store the names of the workbook in the Workbook object so you don't need to activate it.
2) Avoid .Select and directly perform the action on that cell for example you don't need line 19 in the above code.
If you supply the workbook names and explain exactly what you are trying to do then maybe I can cough up some code for you :)
Sid