Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each c In Range... is slow
This works but is a bit clunky and a little slow.
With sheet Reg: Columns A, B, C is Name, Info1, Info2 (a person & two info bits per row) Columns D, E, F, G, H, I are Sheet1, Sheet2, Sheet3 etc. actual sheet names in the workbook, just using six here, could be ~15. If any of the cells in the Sheet Name Header columns have an "X" then the column A, B, C data on that row is copied to the sheet named in the Header of that column. I have set OneRng to include D2 to I- as far down as column A info goes. Then a For Each / Next offsetting from each "X" to copy the A B C info of that row and paste in the sheet name of that column. I'm guessing the search method for looking across the OneRng is not very efficient using this approach. Got a smoother idea, please? Howard Sub Header_Sheet() Dim OneRng As Range Dim sheetTo As String Dim c As Range Dim aRow As Long, aCol As Long Set OneRng = Sheets("Reg").Range("D2:I" & Cells(Rows.Count, "A").End(xlUp).Row) Application.ScreenUpdating = False For Each c In OneRng aRow = c.Row - 1 aCol = c.Column - 1 sheetTo = c.Offset(-aRow, 0).Value If c = "X" Then c.Offset(, -aCol).Resize(1, 3).Copy Sheets(sheetTo).Range("A" & Rows.Count).End(xlUp)(2) End If Next 'c Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each c In Range... is slow
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. -- Well, that is a work of art. Autofilter to the rescue again, and of course the excellent knowledge to use it. Thanks Claus. Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT with date range running slow | Excel Discussion (Misc queries) | |||
Range.Find gets slow | Excel Programming | |||
Looping through Range...Slow | Excel Programming | |||
Any thoughts - VBA Slow Down with Range.Clear Command | Excel Programming | |||
Problem with Slow ReCalculation of Dynamic Range Using OFFSET | Excel Worksheet Functions |