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]

How much text are you catenating?

 

[log0r]

Currently the character count for the range to catenate is 263,000 - these comprise the keywords tied back to SKU's in the original 15,000 part number sheet.

 

[shg]

A cell can only contain 32767 characters.

 

[log0r]

Sorry, I can't have been clear, so those 263,000 characters are in 30,000 cells - no cell has more than approx 200 characters. This one really has me stumped sorry (but nothing else has changed apart from the increased cells that I can tell...)

 

[shg]

You're catenating a bunch of cells into a single cell. My question is, what's the longest result? More specifically, what's the length of the result when the code fails?

 

[log0r]

shg, I'd like to offer an example of my "keywords" sheet (now with 30,700 rows - of which there are 8,000 SKU's (col A)).. it is exported in XLS format and contains the keywords but also some data I don't need.

SKU	Keywords
redcap
	hat - *notes here about hats*
	cap - *notes here about caps*
bluepants
	trousers - *notes here about trousers*
	longs
	leggings
redpants
	trousers
	longs
	leggings
greenshirt
	T-Shirt
	tshirt
	top - *notes here about tops*
blueshirt
	T-Shirt
	tshirt
	top

<colgroup><col><col></colgroup><tbody>
</tbody>

The macro ignores anything in range B after a space " " as that information is needless for my keywords. and then concatenates it alongside the SKU it's supposed to be with (in column A, above).

Does this help give some more context?

 

[shg]

If you put a workbook (that fails) on box.net and post a link, I'll take a look.

 

[log0r]

So when the code fails, and I run debug... every cell in column B has been trimmed (anything after a space removed), the whole range A:B30721 is selected, showing me the start of the spreadsheet with "Ar(1).Offset(-1) = Trim(Join(Application.Transpose(Ar), ", "))" being highlighted... 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?

The largest SKU (col A) to concatenate has 113 cells (in col B) with a total of 1263 characters (so 113 keywords comprising 1263 characters... which that particular SKU and it's keywords have been working for years).

I really appreciate your efforts Shg, this one has me stumped.

 

[shg]

I ran it on the small sample you posted and it worked fine (aside from that you can have an entry in B1).

Without seeing the problem, I have no suggestion.