VBA won't execute for large datasets, but will for small datasets. HELP!

[how]

Hi,

I am currently working in excel 2007 on a spreadsheet containing large raw data amounts (about 700,000-800,000 rows and 20 columns). My macro finds and highlights and records specific sequences of data I want. Testing with smaller datasheets of less than 500 rows, the macro works fine. If i try it on the 700,000+ row spreadsheet, nothing happens. Why is this? Is there a way to get around this limitation assuming my macro doesn't work because the spreadsheet is too large, beyond the limitations of vba?

I am considering trying to use access, but my vba access is not that great, at least not to the point where I can find my sequences via code like I do in excel vba.

 

[tigeravatar]

It's hard to say without seeing your code.

 

[how]

sorry i'm new, but how do I post code in a text box and not as text like this reply?

 

[tigeravatar]

You wrap the text in code tags:
[ Code ]This is where your code goes[ /code ] (but without the spaces):

This is where your code goes

 

[how]

 Sub sequential()

'Find sequence

    Dim guage10 As Range
    Dim intensity As Object
    Dim set1 As Range
    Dim row As Integer
    Dim lastrow As Long
    Dim count As Integer
    Dim i As Integer
    
    
    
    On Error Resume Next
    count = 0
    i = 0
    lastrow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).row
'' find a cell searching within the 2nd row to the last row that, has over 50% of its columns filled (count > 10), is highlighted "8" (prevent doublecounting),
'' and has a mid value of greater than 2.  Then, highlight cells 36 rows above and below.  print the row in which all 3 conditions are met in immediate window
        
        For row = 2 To lastrow
            Set set1 = Range(Cells(row, 2), Cells(row, 22))
            count = 0
            For Each intensity In set1
                If intensity.Value > 0 Then
                    count = count + 1
                End If
            Next intensity
            If count > 10 Then
                        If Range(Cells(row, 2), Cells(row, 2)).Value > 2 Then
                            If Range(Cells(row, 2), Cells(row, 2)).Interior.ColorIndex = "8" Then
                                Debug.Print row
                                Dim timespan As Range
                                If row - 36 < 0 Then
                                    Set timespan = Range(Cells(2, 2), Cells(row, 2))
                                    timespan.Interior.ColorIndex = 4
                                Else
                                    Set timespan = Range(Cells(row - 36, 2), Cells(row, 2))
                                    timespan.Interior.ColorIndex = 4
                                End If
                                If row + 36 > lastrow Then
                                    Set timespan = Range(Cells(row, 2), Cells(lastrow, 2))
                                    timespan.Interior.ColorIndex = 6
                                Else
                                    Set timespan = Range(Cells(row, 2), Cells(row + 36, 2))
                                    timespan.Interior.ColorIndex = 6
                                End If
                            End If
                        End If
            End If
        Next

        
 
End Sub

 

[how]

also note, i highlighted all the cells with color "8" from the start so the default background fill is "8", not null.

 

[tigeravatar]

how,

Code looks good, but it can be made more efficient. You have it searching through every row and checking for color. Since it only goes down, you could implement a while loop instead of a for loop, and if all conditions are met, row + 37, else + 1. Also, could make the time span 2 growing ranges that get highlighted all at once at the end. You can also disable screenupdating, events, and auto-calc while code runs to let it run faster:

application.screenupdating = false
application.enableevents = false
application.calculate = xlcalculationmanual

dim rng4 as range
dim rng6 as range
dim row: row = 2

while row <= lastrow
    If all conditions met
        if row < 38 then
            if rng4 is nothing then
                set rng4 = range(cells(2,2),cells(row,2)
            else
                set rng4 = union(rng4,range(cells(2,2),cells(row,2))
            end if
        else
            if rng4 is nothing then
                set rng4 = range(cells(row-36,2),cells(row,2)
            else
                set rng4 = union(rng4,range(cells(row-36,2),cells(row,2))
            end if
        end if
        
        if row + 36 > lastrow then
            if rng6 is nothing then
                set rng6 = range(cells(row,2),cells(lastrow,2))
            else
                set rng6 = union(rng6,range(cells(row,2),cells(lastrow,2)))
            end if
        else
            if rng6 is nothing then
                set rng6 = range(cells(row,2),cells(row+36,2))
            else
                set rng6 = union(rng6,range(cells(row,2),cells(row+36,2)))
            end if
        end if
        row = row + 37
    else
        row = row + 1
    end if
wend

rng4.interior.colorindex = 4
rng6.interior.colorindex = 6

application.calculation = xlcalculationautomatic
application.enableevents = true
application.screenupdating = true

Hope that helps,
~tigeravatar