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 |
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 |