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

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

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