Resizing set Range

[SakiSam]

Good Day!

This should be an easy one. I am attempting to resize a set range to include data that was split and transposed so I can split it again. However I am receiving "Application-defined or Object-defined error." I'd appreciate anyone's assistance in pointing out what I missed and any other suggestions.

Thank you in advance:

Here's the code:

Sub SplitNTranspose()

Dim rng As Range, rng0 As Range, nPage As String
nPage = ActiveSheet.Name
On Error GoTo Error_NoBars

For r0 = 1 To 3 Step 1 'there will be three sets of these strings in the final version.
    Set rng = Sheets(nPage).Range("A:A").Find(What:="||")
    r1 = (Len(rng.Value) - Len(Replace(rng.Value, "/", "")))
    N = Split(rng, "/")
    rng.Resize(UBound(N) + 1) = WorksheetFunction.Transpose(N)
    Set rng0 = rng.Resize(r1 + 1, 0)             '<----------------------ISSUE: "Application-defined or Object-defined error."
    rng0.TextToColumns _
        Destination:=rng, DataType:=xlDelimited, textqualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=True, OtherChar:="|"
Next r0

Error_NoBars:
End Sub

The Sample data for this is:

567T113||184T448||516T595||516W007||312T652||589T752||567T168/567W470||567W521||590W311||184W240||294W922||312T659||302W367/302W368||061T764||590T815||567W615||567W107||517W596||568W754/579F761||579W942||302W237||184W172||312T662||579W885||184W178/302T098||567T448||579W883||567W446||589W527||579W832||589W893/589T359||061W769||589W898||579W838||567W917||566W972||061W801/590W863||579W106||590W762||061T723||516W722||567C403||302W729/516W499||516W714||516T394||516W711||516W374||579W099||567W696/568W613||589W293||567W590||589W543||294W670||579T724||579W733/294W436||590W948||184W651||590W985||184W674||302W581||589W073/061W665||184W597||590W388||590W721||590W717||061W695||590W719/567W667||184W902||294W936||184W696||567W226||567W116||302W269/565W893||311W941||567W877||579T543||579T504||312T660||184W187/184W183||294W622||184W024||184W999||294W994||184W643||294W666/567W441||302W682||302W695||184W491||589W054||516W875||061W862/302W898||589W300||184W296||589W985||184W394||312T661||294W022/516W097||302W443||589W363||294W208||302T526||302W192||516T588/589W234||589W643||589W333||589W640||579W895||578W202||579W517/567W674||516T364||294W884||590W222||578W297||302T654||313F966/184W516||590T943||302W398||579W609||589W917||579W673||567W992/516W842||302W614||184W961||589W183||294W032||567T120||589W630/516W084||589W553||589W628||184W631||184W632||589W967||579W046/302W324||184W571||184W572||579W033||579W338||302W667

---

Explanation:
This sub takes one of three strings of ItemID's (built and placed by another sub) and displays the id's in a block that is seven items wide by a variable number of rows. Every "/" is a line break and each "|" is a item break. The reason for two(2) bars(|) is to put an extra column between each that will be merged with the ItemID to the left for presentation purposes. This step is not included here.

---

 

[Comfy]

Set rng0 = rng.Resize(r1 + 1, 0)

I've just had a quick read though and this stands out.

Can you define a column width of zero in the Resize Property?

Just tested it and it turns out you can't, this is your problem.
You can ommit the column number but you can't use zero.

 

[SakiSam]

Thank you,
I didn't see anything about that when I looked up Resize, so I assumed it (Resize) worked just like Offset.

Per your suggestion I changed:
Set rng0 = rng.Resize(r1 + 1,0)
to
Set rng0 = rng.Resize(r1 + 1, 1)
and the sub worked exactly as I wanted it to.

Thanks again!!