Loop through visible cells only

[zanac]

[FORMULA]Please Login or Register to view this content.[/code]

[hrlngrv]

Any time VBA macros seem to take a very long time to run, it's likely best to consider adding the following code.

Please Login or Register  to view this content.

See if that doesn't speed up your code.

Anyway, in code, you can check whether the kth row of range rng isn't hidden due to a filter using

If Application.WorksheetFunction.Subtotal(103, rng.Rows(k)) > 0 Then . . .

Note: the function will return 0 if row k is hidden due to a filter OR if all cells in that row are blank and it's not hidden by the filter. It'll return a positive number if the row is visible (passed through the filter) AND contains at least one nonblank cell.

[juddaaaa]

You can use Range.SpecialCells(xlCellTypeVisible) to just loop over the visible cells

Something like this should do it...

Please Login or Register  to view this content.

[zanac]

Thank you hrlngrv, that helps to reduce the time little bit

[zanac]

Any time VBA macros seem to take a very long time to run, it's likely best to consider adding the following code.

Please Login or Register  to view this content.

See if that doesn't speed up your code.

Anyway, in code, you can check whether the kth row of range rng isn't hidden due to a filter using

If Application.WorksheetFunction.Subtotal(103, rng.Rows(k)) > 0 Then . . .

Note: the function will return 0 if row k is hidden due to a filter OR if all cells in that row are blank and it's not hidden by the filter. It'll return a positive number if the row is visible (passed through the filter) AND contains at least one nonblank cell.

Thank you hrlngrv, that helps to reduce the time little bit

[zanac]

You can use Range.SpecialCells(xlCellTypeVisible) to just loop over the visible cells

Something like this should do it...

Please Login or Register  to view this content.

That actually worked perfectly!! thank you so much Juddaaaa!!

[hrlngrv]

I was hoping not to be tempted to rewrite this, but oh well.

Please Login or Register  to view this content.

The main idea here is that it's most efficient to load worksheet range data into VBA arrays in as few VBA operations as possible, and likewise return as much data from VBA arrays to worksheet ranges in as few VBA operations as possible.

In this case, I load the values of E2:En into arr1 and R2:Rn into arr2 in single VBA statements, then process those arrays, then return the values in arr2 to R2:Rn in a single VBA statement. Then I clear arr1 and load it with the values from P2:Pn, and continue processing arr2, which again gets returned to R2:Rn.

If you need P2:Pn or R2:Rn to recalc based on the results of the 1st For loop, you need the rngAll.Columns(18).Value2 = arr2 statement between the 1st and 2nd For loops. If P2:Pn and R2:Rn are all constants, then you could remove that statement.

The only operation inside any of the For loops which needs to be done to worksheet cells directly is setting the Interior (cell background) color. Note, however, that color is uninitialized within this procedure. Is it a global variable? If not, learn to start all VBA modules with Option Explicit.

Final thing to note, your original code's 2nd and 3rd For loops were

Please Login or Register  to view this content.

which meant that you were redundantly processing rows. That is, if C = 2, 3 and 4 were all visible, you would have compared rows D = 3 and 4 to row C = 2, so there was no reason to compare row D = 2 to row C = 3 or 4.