Help speed up my for loop

edwardtong694

Board Regular
Joined
Aug 21, 2009
Messages
125
Hi Guys,

I have a for loop which is as follows.

Code:
 'Do something with all the data between C27 and c37
    For Each rng In Range("C27:C37")
    'if there is data in the cell then
    If Not IsEmpty(rng) Then
        'List the DDI and then plus one until all numbers are complete.
        For x = 0 To rng.Offset(0, 2).Value - rng.Value
            'Print the information into the lists field.
            Sheets("Lists").Cells(Rows.Count, "K").End(xlUp).Offset(1, 0) = rng.Value + x
        Next X

The code will loop through numbers in Cell C27 and C37 and list out all the values between values in cell c27 and d27 and so on and then copy's them to cell K. The code works as expected but it is slower than i think it should be.

I have stepped through the code and it looks as if once it has completed it starts again and again when it doesn't need to. I want the formula to ignore blank cells.

I was wondering if anyone could take a look and see if they can see anything obvious i am missing.

Thanks

Ed
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Ed

That code doesn't look at column C and D it looks at C and E.

Try this, change the offset if it is meant to look at C & D instead of C & E.
Code:
Dim rng As Range
Dim X As Long

    For Each rng In Range("C27:C37")

        'if there is data in the cell then
        If Not IsEmpty(rng) Then
            X = rng.Offset(, 2).Value - rng.Value + 1

            With Sheets("Lists").Cells(Rows.Count, "K").End(xlUp).Offset(1, 0)
                .Value = rng.Value
                .Resize(X).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1
            End With
        End If


    Next rng
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,322
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top