VBA Macro breaks @ >30,000 rows

[log0r]

Hi all,

I've been running a VBA macro dealing with 2 xls sheets, one with approx 15,000 rows, and one with now over 30,000 rows. Somewhere between 30,000 and 30,700 I am now up to, the part of my macro which concatenates the 30,000 row spreadsheet and then pastes into the 15,000 row spreadsheet breaks.

The error I get is "Run-time error '1004': Application-defined or abject-defined error
This is while running "Ar(1).Offset(-1) = Trim(Join(Application.Transpose(Ar), ", "))" in the following piece of VBA:

Sub ConcatBetweenBlanks()
  Dim Ar As Range
  For Each Ar In Columns("B").SpecialCells(xlConstants).Areas
    If Ar.Count = 1 Then
      Ar(1).Offset(-1) = Ar
    Else
      Ar(1).Offset(-1) = Trim(Join(Application.Transpose(Ar), ", "))
    End If
  Next
  Columns("A").SpecialCells(xlBlanks).EntireRow.Delete
End Sub

After some googling I've found VBA may have a limit at 32,000 rows of data to copy/paste. I'm not convinced this is my issue though as I'm just shy of this number. If this is the issue, I don't understand how exactly I'm likely to be able to work around it - as in the future these rows are only going to increase to somewhere around 60,000.

As always any help or advice would be very much appreciated!

 

[Rick Rothstein]

So not one part of concatenation has begun (which isn't giving me any hint as to which part is too long, apart from the whole of A:B right?

What I highlighted focused me right in on the problem... delete the Keyword header text or change your range to start at cell B3. The Keyword header is a constant, so it is the first Area the Ar references in your For Each loop. The problem occurs when you try to reference the cell above it with this...

Ar(1).Offset(-1)

There is no cell above cell B1, hence the error.

 

[Fluff]

The macro ignores anything in range B after a space " " as that information is needless for my keywords.

Is this a separate macro? as the one you posted won't do that.
Also what is the address of AR when it fails?

 

[shg]

(aside from that you can have an entry in B1).

aside from that you can't have an entry in B1.

 

[log0r]

Thanks for the suggestions, and my apologies for my lack of clarity... there are macros running before this one (I just ran through step by step from the start to get my head around this once again, it's been working fine every day for a few years... so takes me awhile to familiarize myself with what each part achieves).

So I can confirm that this "keywords sheet" gets cleansed, in the sense that the header row is taken away (so B1 is empty) and that the extra data I talked about is already removed, without fault.

Fluff, when you ask for the address of AR when it fails, I might not have this right, but it fails immediately (in as soon as my highlighted step is attempted to be run) so presumably right at the beginning.. as the step before takes a good 10-15 seconds to process. Does this give any further insight?

I can certainly say that the only change has been to add more keywords (spread across many SKU's, so no one particular large keyword set etc), and if I delete say 1000 rows off the native sheet to 29,000 for example, it works no problem at all...

 

[Rick Rothstein]

Thanks for the suggestions, and my apologies for my lack of clarity... there are macros running before this one (I just ran through step by step from the start to get my head around this once again, it's been working fine every day for a few years... so takes me awhile to familiarize myself with what each part achieves).

So I can confirm that this "keywords sheet" gets cleansed, in the sense that the header row is taken away (so B1 is empty) and that the extra data I talked about is already removed, without fault.

I would like to refer you to what shg posted in Message #8 ... if you upload a copy of your workbook to DropBox that stops right after the above indicated macros have run (that is, don't run the macro that isn't working... save off a copy of the workbook up to that point and post it), then you have at least three volunteers here who will look at it to try and determine the problem for you. Simply telling us it worked before but doesn't work now tells us nothing useful... if we can see the data it doesn't work on, then we will have something to work with. If you have sensitive information, replace each unique sensitive item along with any repeats of it with the same non-sense text (that should not affect the problem you are having).

 

[log0r]

Thanks Rick and all, the data is sensitive and will take me some time to cleanse - I will begin doing this shortly.

In the mean time I was running the macro on a test sheet over and over, and found that it works perfectly up to 30,397 rows, and when I add the next SKU putting total row count to 30,404 it errors at the third step. Again, now working on offering a sheet - thanks for your time guys.

 

[log0r]

Hi guys - OK so I've realised I can replicate the issue without my unique SKU's and Keywords - so have replaced them with TESTSKU and TESTKEYWORD - I've left the VB in the sheet to run too, it will break with the 30,404 lines, but if you delete the last TESTSKU and it's keywords off reducing the rows to 30,397 the macro works (it's slow, but works).

https://app.box.com/s/re8yvn8grnf5m5b8tjqpe829095dbc54

Thanks for your expert eyes.

 

[shg]

Worked fine here.

 

[offthelip]

If you ever solve your problem and get this to run it will going to take a very long time to run, so this is very inefficient way of coding. The way I would do this is using variant arrays which will make it about 1000 times faster, and since your technique seems to fall over you might try this simple way using variant arrays.
I say this so often on the forum that I have the following paragraph ready to paste whenever this issue comes up, so for your information:
One of the main reasons that Vba is slow is the time taken to access the worksheet from VBa is a relatively long time.
To speed up vba the easiest way is to minimise the number of accesses to the worksheet. What is interesting is that the time taken to access a single cell on the worksheet in vba is almost identical as the time taken to access a large range if it is done in one action.
So instead of writing a loop which loops down a range copying one row at a time which will take along time if you have got 50000 rows it is much quicker to load the 50000 lines into a variant array ( one worksheet access), then copy the lines to a variant array and then write the array back to the worksheet, ( one worksheet access for each search that you are doing),
I have a simple rule for fast VBA: NEVER ACCESS THE WORKSHEET IN A LOOP.
The code for you:

Sub concat()
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
' pick up all the data
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
' define the output array
outarr = Range(Cells(1, 1), Cells(lastrow, 1))
' clear the entire sheet
Range(Cells(1, 1), Cells(lastrow, 2)) = ""
tempstr = ""
' loop through the data starting at the bottom
For i = lastrow To 1 Step -1


  If inarr(i, 2) = "" Then
' blank row so output the concatenation
   outarr(i, 1) = outarr(i, 1) & "," & tempstr
   tempstr = ""
  Else
 ' not blan so add to temporary string
   tempstr = tempstr & "," & inarr(i, 2)
  End If
Next i
' output the output array
Range(Cells(1, 1), Cells(lastrow, 1)) = outarr




End Sub

Note that you could change the output index to eliminate the blank rows at the same time, by using a separate index for the output.

 

[log0r]

Worked fine here.

Really?! Interesting. So I'm running excel 2007, on windows 10 64bit - I have also tried the macro on Excel 2007 running Windows 7 64bit and get the same result.

offthelip - thanks so much I really appreciate your insight and will attempt your macro shortly! Cheers.