Skipping a row in a loop if a cell value = 0 2

[APElliott]

Hello,

I have the bit of code below that takes quite a while to complete, so I'm trying to speed it up!

The code loops from row 2 through to row 820, but it could skip certain rows if the row value in say column Z equalled 0.

Is this possible?

Code:

Sheets("Material&quot.Select
[k1] = [l1]
[j2:j820].Value = [c2:c820].Value
Dim R As Long
'Loop till R = 820
For R = 2 To 820
'Cells(RowNumber,ColNumber)
Cells(R, 3).FormulaR1C1 = ""
Cells(R, 12).Value = Cells(R, 11).Value
Cells(R, 3).Value = Cells(R, 10).Value
Next R

Thanks

Andrew

 

[RoyVidar]

An IF-statement should do the trick

Sheets("Material&quot.Select
[k1] = [l1]
[j2:j820].Value = [c2:c820].Value
Dim R As Long
'Loop till R = 820
For R = 2 To 820
'Cells(RowNumber,ColNumber)
if cells(R,&quot;your row number&quot.value<>0 then
Cells(R, 3).FormulaR1C1 = &quot;&quot;
Cells(R, 12).Value = Cells(R, 11).Value
Cells(R, 3).Value = Cells(R, 10).Value
end if
Next R

HTH Roy-Vidar

 

[SkipVought]

Hi,

I took you code and timed the duration and it runs virtually instantly.

I'm guessing that you have lots of formulas doing calculations. So set calculation to manual and then back to auto if you want

    Calculation = xlCalculationManual
...
    Calculation = xlCalculationAutomatic

See my faq faq707-4105 How Can I Make My Code Run Faster?

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[APElliott]

Cheers Guys, I'll give them a go!

I don't know why, but when this code was first created it was timed a 12 seconds, but since then it as slowed to about 45 seconds, and this is a problem because I have another code that runs this code several times.

Thanks,

Andrew

 

[TonyJollans]

Hi Skip,

I agree there's no obvious performance sink in the code but I don't think APElliott can just switch off calculation because the setting of Cells(R,3) twice implies it is involved in a calculation which won't then happen at the right time.

I suspect there are several ways to speed it up including taking control of what is calculated when, but I'd like to know a bit more about it first.

Enjoy,
Tony

 

[kphu]

I'm not sure if this would apply to what you have but I find turning screenupdating off to speed up my worksheets as well.

application.screenupdating = false
Sheets(&quot;Material&quot.Select
[k1] = [l1]
[j2:j820].Value = [c2:c820].Value
Dim R As Long
'Loop till R = 820
For R = 2 To 820
'Cells(RowNumber,ColNumber)
Cells(R, 3).FormulaR1C1 = &quot;&quot;
Cells(R, 12).Value = Cells(R, 11).Value
Cells(R, 3).Value = Cells(R, 10).Value
Next R
application.screenupdating = true

ken

 

[SkipVought]

If Calculation is critical to values in the target cell, then maybe a calculate could be performed on ONLY that/those cell(s) that are referenced by the cell.

By referencing the range with calculate...

application.screenupdating = false
Sheets("Material").Select
    [k1] = [l1]
    [j2:j820].Value = [c2:c820].Value
    Dim R As Long
    'Loop till R = 820
    For R = 2 To 820
       Set rngCalc = Range(whateverRange)
       rngCalc.Calculate
        'Cells(RowNumber,ColNumber)
        Cells(R, 3).FormulaR1C1 = ""
        Cells(R, 12).Value = Cells(R, 11).Value
        Cells(R, 3).Value = Cells(R, 10).Value
    Next R
application.screenupdating = true

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[JohnAcc]

Why do you need to set

Cells(R, 3).FormulaR1C1 = &quot;&quot;

From the code you have posted why not

[c2:c820].ClearContents

This will take a line of code out of your loop

Rgds

John

 

[APElliott]

Hello All,

Thanks for looking at my problem.

Setting the application.screenupdating = false and then to true speeds it up a fair bit.

I'll try to explain what the code is doing.

Within range [C2:C820] I have a list of construction material prices (each cell is named to suit the material).

I also have a sheet called &quot;BoQ&quot;. Here I write simple formulae. These formulae select one of the above named cells and times it by a quantity.

Example: =MF10.01FacingBricks*.0593+MZ10.01Mortar*.03

I then perform a subtotal function on the BoQ sheet. This gives me Totals for Labour, Plant & Materials at changes in page number. I also have a BoQ Subtotal formula on the Material sheet in cell [L1] SUBTOTAL(9,BoQ!O3:$O$65536).

The code:

Firstly the code takes the Subtotal value in cell [L1] and transfers the ‘Value’ to cell [K1].

Then a copy of all the material prices in range [c2:c820] are copied to range [J2:J820].

Next the code deletes the material price in the first row in column C. If this named cell has been used in a formula on the BoQ sheet then the Subtotal formula changes.

Next the formula on the same row in column K (=$K$1-$L$1) is copied to column L as a value.

Then the price for the material is transferred back to column C from column J.

The code then loops to the next row and so on.

Hope this help.

Thanks,

Andrew

 

[navyguy]

It has been my experience that interfacing with excel one cell at a time in a VB loop can be slow even with tricks. However, when I set a ranges in excel equil to arrays in VB, perform the manipulations in VB and pass the entire array back to excel, the procedure becomes nearly instantaneous.

Bits of the code you would need - I may not have all the syntax correct.

Dim MyRange as range
Dim MyArray

Set MyRange = [A1:A800]

MyArray = MyRange

<code manipulation>

MyRange = MyArray

 

[APElliott]

Hi Navyguy,

Sorry I've only just responded to your help - I must of somehow missed my email message!

Anyway - instantaneous sounds great to me, but I a bit thick, so if you could help a bit more I would be grateful.

Cheers,

Andrew