Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a faster way to update values selected by advanced filter
I've got several worksheets with +30.000 rows, in one column (Q:Q) I need to
fill in values based on different criteria. For that I use Advanced Filter(s) to determine which rows should be updated with values from an Array, SLA(). Subsequently I loop through all rows in activesheet.usedrange. If the row is not Hidden, then the column is updated. here is part of the code: Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("SelectSLA"), Unique:=False Range("Q1").Select For RData = 2 To ActiveSheet.UsedRange.Rows.Count If Not Rows(RData).Hidden Then Range("Q" & RData).FormulaR1C1 = SLA(Rsla, UBound(SLA, 2)) End If Next But this code is very slow. Does anyone know a faster way, to do this. I need to repeat this section with 30-75 different criteria (=Range("SelectSLA") is composed using af different loop) /Tommy, DK |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a faster way to update values selected by advanced filter
I don't know If this will help. But you can create a union of cells at the
beginning of the code and then use the union over aggain later in the code. if you are using different coloumns then add an offset. Sub test() First = True For RData = 2 To ActiveSheet.UsedRange.Rows.Count If Not Rows(RData).Hidden Then If First = True Then Set NewRange = Range("Q" & RData) First = False Else Set NewRange = Union(NewRange, Range("Q" & RData)) End If End If Next RData NewRange.FormulaR1C1 = SLA(Rsla, UBound(SLA, 2)) End Sub "TG4600, DK" wrote: I've got several worksheets with +30.000 rows, in one column (Q:Q) I need to fill in values based on different criteria. For that I use Advanced Filter(s) to determine which rows should be updated with values from an Array, SLA(). Subsequently I loop through all rows in activesheet.usedrange. If the row is not Hidden, then the column is updated. here is part of the code: Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("SelectSLA"), Unique:=False Range("Q1").Select For RData = 2 To ActiveSheet.UsedRange.Rows.Count If Not Rows(RData).Hidden Then Range("Q" & RData).FormulaR1C1 = SLA(Rsla, UBound(SLA, 2)) End If Next But this code is very slow. Does anyone know a faster way, to do this. I need to repeat this section with 30-75 different criteria (=Range("SelectSLA") is composed using af different loop) /Tommy, DK |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a faster way to update values selected by advanced filter
Thank you, I did a test and the run-time was reduced from 44 sec to 38 sec.
In the mean time I have discovered that if I from a different workbook open this workbook and the VBA Sub Auto_Open runs this code the run-time is heavily increased. "Joel" wrote: I don't know If this will help. But you can create a union of cells at the beginning of the code and then use the union over again later in the code. if you are using different columns then add an offset. Sub test() First = True For RData = 2 To ActiveSheet.UsedRange.Rows.Count If Not Rows(RData).Hidden Then If First = True Then Set NewRange = Range("Q" & RData) First = False Else Set NewRange = Union(NewRange, Range("Q" & RData)) End If End If Next RData NewRange.FormulaR1C1 = SLA(Rsla, UBound(SLA, 2)) End Sub "TG4600, DK" wrote: I've got several worksheets with +30.000 rows, in one column (Q:Q) I need to fill in values based on different criteria. For that I use Advanced Filter(s) to determine which rows should be updated with values from an Array, SLA(). Subsequently I loop through all rows in activesheet.usedrange. If the row is not Hidden, then the column is updated. here is part of the code: Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("SelectSLA"), Unique:=False Range("Q1").Select For RData = 2 To ActiveSheet.UsedRange.Rows.Count If Not Rows(RData).Hidden Then Range("Q" & RData).FormulaR1C1 = SLA(Rsla, UBound(SLA, 2)) End If Next But this code is very slow. Does anyone know a faster way, to do this. I need to repeat this section with 30-75 different criteria (=Range("SelectSLA") is composed using af different loop) /Tommy, DK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Advanced Filter: How do you MOVE the selected rows... | Excel Worksheet Functions | |||
Updating values using advanced filter - VBA | Excel Programming | |||
Advanced Filter - Automatic Update | Excel Worksheet Functions | |||
Advanced Filter for Values in Column M greater than Values in Colu | Excel Discussion (Misc queries) | |||
Advanced Filter - Unique Values | Excel Programming |