optimizing code? (hide)

[Johan Johansson]

I have a drop-down list from where the user can select which day he
wants to see.

At first I hide everything, after that I run the little procedure
below

The problem that I'm having is that this takes lots of time running
this. How do I change the procedure so it will run faster?

Sub show_days()

Dim compare
Application.ScreenUpdating = False

compare = Range("F10").Value

Range("F13").Select

For n = 1 To 8000
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = compare Then
ActiveCell.EntireRow.Hidden = False
End If
Next n

Application.ScreenUpdating = True

End Sub

/Johan

 

[Chip Pearson]

Johan,

There is no reason to Select or Activate a cell within the loop.
Instead, try something like

For N = 13 To 8013
If Cells(N,"F").Value = compare Then
Rows(N).Delete
End If
Next N


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Chip Pearson]

Rows(N).Delete

should be
Rows(N).Hidden = False


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Jake Marx]

Hi Johan,

Another option is to use an Auto or Advanced Filter to display the data you
want:

Sub test2()
With Sheet1
If Len(.Range("F11").Value) Then
.Range("F13").CurrentRegion.AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=.Range("F10:F11")
Else
On Error Resume Next
.ShowAllData
On Error GoTo 0
End If
End With
End Sub

To use this, you should have field names above your data. In my test, I put
"ID" in F13 and "Name" in G13. Then, you need to specify your criteria. In
my case, I used F10="ID" and F11=1. When you run the code above, you will
see only those items in your list where ID=1. If you clear out the value
from F11, you'll see all the data. You could even trigger this code on the
Worksheet_Change event so the list is filtered automatically when the user
changes F11.

This should be much faster than doing it programmatically. I tested this on
8,900 rows of data, and the code executed in milliseconds.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]