For Each c In Range... is slow
Hi Howard,
Am Sat, 26 Sep 2015 03:15:09 -0700 (PDT) schrieb L. Howard:
I'm guessing the search method for looking across the OneRng is not very efficient using this approach.
try again autofilter:
Sub Test()
Dim LCol As Long, LRow As Long, i As Long
Dim myRng As Range
Application.ScreenUpdating = False
With ActiveSheet
LCol = .Cells(1, Columns.Count).End(xlToLeft).Column
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set myRng = .Range(.Cells(1, 1), .Cells(LRow, LCol))
For i = 4 To LCol
myRng.AutoFilter Field:=i, Criteria1:="<"
.Range(.Cells(2, 1), .Cells(LRow, 3)).SpecialCells(xlCellTypeVisible).Copy _
Sheets(.Cells(1, i).Value).Cells(Rows.Count, 1).End(xlUp)(2)
.ShowAllData
Next
End With
Application.ScreenUpdating = True
End Sub
Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
|