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!

 

[shg]

So I'm running ...

32-bit Excel 2010 on Windows 7 64-bit.

 

[log0r]

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.

That you offthelip, your code works (incredibly fast as you say), even when I double the data to 60,000 rows which is great. However it's not quite giving me the result of my previous code (and although I like your explanations within the code, I don't understand if I can achieve my desired end result with it).

In the end, I'd like the SKU's still populated in column A, and their appropriate keywords popuated beside them in column B (and comma separated). Ideally, yes the blanks would be removed but I believe I could achieve that in other/later run macros. Currently the code gives the SKU & its keywords all in the same cell in column A. Are you kindly able to explain what I need to alter or offer a solution to maintain the A & B columns?

 

[log0r]

offthelip - I've managed to achieve the result I wanted myself - sorry to have bothered you again, and thanks to you others for the help along the way!

This code ended up giving me the same results without crashing @ 30,404 rows, and doing it in a fraction of the time:

Sub ConcatBetweenBlanks()
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))
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) = tempstr
   tempstr = ""
  Else
 ' not blank so add to temporary string
   tempstr = tempstr & "," & inarr(i, 2)
  End If
Next i
' output the output array
Range(Cells(1, 2), Cells(lastrow, 2)) = outarr


End Sub

Cheers.