Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!
Learn how Concerto can help you.
Solved
Replacing a "Do... Loop Until" routine with something a littlef aster
Posted on 2011-03-02
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
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 = False
Do
ActiveWindow.ActivateNext
Dim str As String
str = ActiveCell.Value
ActiveCell.Copy
ActiveCell.Offset(1, 0).Activate
ActiveWindow.ActivateNext
Cells.Find(What:=str, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.EntireRow.Interior.ColorIndex = 45
ActiveCell.Offset(0, -1) = 1
Dim top As Range, btm As Range
Set top = [C6]
Set btm = ActiveCell
Range(top, btm).Select
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
Loop Until IsEmpty(ActiveCell)
[Counts].Calculate
[C2].Activate
Application.ScreenUpdating = True
End Sub
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3
2-
2
7 Comments
>>> Is there a way to do this faster?
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
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
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.
Thanks,
John
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.
Thanks,
John
John,
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
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
<hmm, fell off again>
For Each cel In Range(top, btm)
If cel.EntireRow.Interior.ColorIndex <> 45 Then
cel.EntireRow.Hidden = True
Else
End If
Next
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.
Sid
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 = True
End Sub
Sid
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.
Sid
wb2.close (True)
True is to close the workbook with changes saved. If you want to ignores changes then replace true by false.
Sid
Thanks Sid. Your code would probably work if i could figure out how to adapt it properly but couldn't get it to work. However, as with the other question (http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26861872.html#a35029686), I took your reminder to avoid selecting so much and came up with something that does it in about 4 seconds.
So thanks again, again!
- John
So thanks again, again!
- John
Sub FindMatches()
Application.ScreenUpdating = False
[C6].Activate
Do
ActiveWindow.ActivateNext
Dim str As String
str = ActiveCell.Value
If str = "" Then
ActiveWindow.ActivateNext
[Counts].Calculate
Exit Sub
Else
End If
ActiveCell.Copy
ActiveCell.Offset(1, 0).Activate
ActiveWindow.ActivateNext
Cells.Find(What:=str, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart).Activate
ActiveCell.EntireRow.Interior.ColorIndex = 45
ActiveCell.Offset(0, -1) = 1
Dim top As Range, btm As Range
Set x = ActiveCell.Offset(0, -1)
Set btm = x.Offset(-1, 0)
Set top = x.End(xlUp).Offset(1, 0)
If btm = "" Then
Range(top, btm).Select
Range(top, btm).EntireRow.Hidden = True
Else
End If
btm.Offset(1, 1).Activate
Loop Until IsEmpty(ActiveCell)
[Counts].Calculate
[A2].Activate
Application.ScreenUpdating = True
End Sub
Featured Post
Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Introduction
This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results.
Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210 (2 * 3 * 5 * 7) or 2310 (2 * 3 * 5 * 7 * 11).
The larger templa…
Suggested Courses
Course of the Month8 days, 20 hours left to enroll
604 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.