Is there a "go faster" option to hide rows

[Dav1d]

I'm working with a form that can have varying rows of data. There will come a time when I want to archive them according to a criteria ("3" in Column A). The code I'm using is below

My problem is its going really, really slow, even when working through 10 or so lines. Is there a tip that might speed things up?

Sub NewArchive()
For Each cell In ActiveSheet.Range("A17:A500")
If cell.Value = "3" Then cell.EntireRow.Hidden = True
Next
End Sub



Many thanks guys!
David

 

[shg]

How about autofilter?

http://www.contextures.com/xlautofilter01.html

 

[Dav1d]

How about autofilter?

http://www.contextures.com/xlautofilter01.html

That's a good thought although the form is going to be used by others and I was hoping for a little more sophistication...

I appreciate your input. Thank you

 

[Weaver]

Don't hide them one at a time. Combine each cell with a 3 in the range and then do an .entirerow.hidden = true on that range.

Sub NewArchive()
    Dim threeRange As Range, cell As Range
    For Each cell In ActiveSheet.Range("A17:A500")
        If cell.Value = "3" Then
            If threeRange Is Nothing Then
                Set threeRange = cell
            Else
                Set threeRange = Union(threeRange, cell)
            End If
        End If
    Next
    If Not threeRange Is Nothing Then threeRange.EntireRow.Hidden = True
End Sub

Having said that, there's not much wrong with filtering. Maybe you could use the advanced filter instead if you don't want the listboxes to appear.

 

[Dav1d]

Weaver, many thanks. I think you've given me the fish and taught me to fish too!

 

[Rick Rothstein]

Weaver, many thanks. I think you've given me the fish and taught me to fish too!

There may be a faster way to do what you want. What is in Column A generating your numbers (the 3 in particular)... constants or formulas? What is the maximum number of rows your data could possibly have (I'm not looking for an exact number, just a ball-park amount... 100, 1000, 10000, etc.)?

 

[Dav1d]

There may be a faster way to do what you want. What is in Column A generating your numbers (the 3 in particular)... constants or formulas? What is the maximum number of rows your data could possibly have (I'm not looking for an exact number, just a ball-park amount... 100, 1000, 10000, etc.)?

Hi Rick

Its a formula: an If statement checking several criteria. Ballpark is circa 500

Thank you

 

[Weaver]

If your headers are in row 16 (as I'm guessing), here's a method using advanced filter, which might be faster than the previous version for larger datasets.

Sub Macro1()
    Dim ws, cs, fr
    Application.ScreenUpdating = False
    Set ws = ActiveSheet
    Set fr = Range("A16", Cells(Rows.Count, "A").End(xlUp))
    Set cs = Sheets.Add
    Range("A1") = ws.Range("A16")
    Range("A2") = "<>3"
    Set cr = cs.Range("A1:A2")
    fr.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=cr, Unique:=False
    Application.DisplayAlerts = False
    cs.Delete
    Application.DisplayAlerts = True
End Sub

 

[Rick Rothstein]

Give this code a try...

Sub HideRowsWith3InColumnA()
  Dim LastRow As Long, UnusedColumn As Long
  Const StartRow = 17
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  With Cells(StartRow, UnusedColumn).Resize(LastRow - StartRow + 1)
    .FormulaR1C1 = "=IF(RC1=3,""X"","""")"
    .Value = .Value
    .SpecialCells(xlCellTypeConstants).EntireRow.Hidden = True
    .Clear
  End With
End Sub

 

[Dav1d]

Give this code a try...

[/CODE]

Wow! That was fast! Many thanks Rick.