split cells & copy across rows - what's wrong with my vba?

[cam394]

Hi All!

First up, new poster, and VBA isn't my best programming language. So, apologies if I've stuffed up something stupid -- I'm learning

Now, I've got a database full of rows of different cases, and I'm trying to split the cases so that there is only one "citing judge" per row. Each judge is split by an alt+enter char (char 0011 I believe?).

I'll show you what I mean...

This is what I have, and I want to split down the highlighted column so that each judge has his own line, with the surrounding data copied into it.

<style type="text/css"> body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Calibri"; font-size:x-small } </style>

Case	Year	Coram	Use of ext laws	Summary	Int'l law cited	Section/Article	Citing judge	Int'l judgments cited	Citing judge 2
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Minister for Immigration and Ethnic Affairs v Teoh[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Mason CJ Brennan J Deane J Dawson J Toohey J Gaudron J McHugh J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]<summary>[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]ECHR[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]n/a[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]McHugh J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]R v Chief Immigration Officer, Heathrow Airport;Ex parte Salamat Bibi [1976] 1 WLR 979[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]McHugh J[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Sleiman v Murray[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Higgins CJ Gray P Ryan J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]<summary>[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]ECHR[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Article 8[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Higgins CJ Gray P Ryan J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]T. v the United Kingdom no. 24724/94 Case of S. and Marper v United Kingdom[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Higgins CJ Gray P Ryan J[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]BEJ15 v Minister for Immigration and Border Protection[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Reeves J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]<summary>[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]ECHR[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Article 3[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Reeves J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]n/a[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Reeves J[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Conway v Jerram[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Barr AJ[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]<summary>[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]ECHR[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]n/a[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Barr AJ[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]R (Sacker) v West Yorkshire Coroner [2004] UKHL 11 [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Barr AJ[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Johnstone v New South Wales[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Beazley JA McColl JA Young JA[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]<summary>[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]ECHR[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]n/a[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Beazley JA[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Fox v UK (1991) 13 EHRR 157[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Beazley JA[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]D'Orta-Ekenaike v Victoria Legal Aid[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Gleeson CJ McHugh J Gummow J Kirby J Hayne J Callinan J Heydon J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]<summary>[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]ECHR[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Article 6[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Gleeson CJ Gummow J Hayne J Heydon J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Osman v United Kingdom (1998) 29 EHRR 245 Arthur J S Hall & Co v Simons[2002] 1 AC 615 [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Gleeson CJ Gummow J Hayne J Heydon J[/COLOR]

<colgroup width="80"></colgroup> <colgroup width="12"></colgroup> <colgroup width="80"></colgroup> <colgroup width="25"></colgroup> <colgroup width="80" span="3"></colgroup> <colgroup width="85"></colgroup> <colgroup width="80" span="2"></colgroup> <tbody>
</tbody>

This is how I want it to look...
<style type="text/css"> body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Calibri"; font-size:x-small } </style>

Case		Citation	Coram	Use of ext laws	Summary	Int'l law cited	Section/Article	Citing judge	Int'l judgments cited	Citing judge 2
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Minister for Immigration and Ethnic Affairs v Teoh[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ][1995] HCA 20[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Mason CJ Brennan J Deane J Dawson J Toohey J Gaudron J McHugh J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]<summary>[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]ECHR[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]n/a[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]McHugh J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]R v Chief Immigration Officer, Heathrow Airport;Ex parte Salamat Bibi [1976] 1 WLR 979[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]McHugh J[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Sleiman v Murray[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ][2010] ACTCA 2[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Higgins CJ Gray P Ryan J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]<summary>[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]ECHR[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Article 8[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Higgins CJ[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]T. v the United Kingdom no. 24724/94 Case of S. and Marper v United Kingdom[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Higgins CJ[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Sleiman v Murray[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ][2010] ACTCA 2[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Higgins CJ Gray P Ryan J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]<summary>[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]ECHR[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Article 8[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Gray P[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]T. v the United Kingdom no. 24724/94 Case of S. and Marper v United Kingdom[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Gray P[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Sleiman v Murray[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ][2010] ACTCA 2[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Higgins CJ Gray P Ryan J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]<summary>[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]ECHR[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Article 8[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Ryan J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]T. v the United Kingdom no. 24724/94 Case of S. and Marper v United Kingdom[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Ryan J[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]BEJ15 v Minister for Immigration and Border Protection[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ][2016] FCA 1033[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Reeves J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]<summary>[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]ECHR[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Article 3[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Reeves J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]n/a[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Reeves J[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Conway v Jerram[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ][2010] NSWSC 371[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Barr AJ[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]<summary>[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]ECHR[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]n/a[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Barr AJ[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]R (Sacker) v West Yorkshire Coroner [2004] UKHL 11 [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Barr AJ[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Johnstone v New South Wales[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ][2010] NSWCA 70[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Beazley JA McColl JA Young JA[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]<summary>[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]ECHR[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]n/a[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Beazley JA[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Fox v UK (1991) 13 EHRR 157[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Beazley JA[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]D'Orta-Ekenaike v Victoria Legal Aid[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ][2005] HCA 12[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Gleeson CJ McHugh J Gummow J Kirby J Hayne J Callinan J Heydon J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]<summary>[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]ECHR[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Article 6[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Gleeson CJ[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Osman v United Kingdom (1998) 29 EHRR 245 Arthur J S Hall & Co v Simons[2002] 1 AC 615 [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Gleeson CJ[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]D'Orta-Ekenaike v Victoria Legal Aid[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ][2005] HCA 12[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Gleeson CJ McHugh J Gummow J Kirby J Hayne J Callinan J Heydon J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]<summary>[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]ECHR[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Article 6[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Gummow J [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Osman v United Kingdom (1998) 29 EHRR 245 Arthur J S Hall & Co v Simons[2002] 1 AC 615 [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Gummow J [/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]D'Orta-Ekenaike v Victoria Legal Aid[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ][2005] HCA 12[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Gleeson CJ McHugh J Gummow J Kirby J Hayne J Callinan J Heydon J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ] [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]<summary>[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]ECHR[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Article 6[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Heydon J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Osman v United Kingdom (1998) 29 EHRR 245 Arthur J S Hall & Co v Simons[2002] 1 AC 615 [/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ]Heydon J[/COLOR]

<colgroup width="80"></colgroup> <colgroup width="8"></colgroup> <colgroup width="80" span="2"></colgroup> <colgroup width="13"></colgroup> <colgroup width="80" span="3"></colgroup> <colgroup width="85"></colgroup> <colgroup width="80" span="2"></colgroup> <tbody>
</tbody>

I tried writing a macro to do this, but I'm not sure where I went wrong.

Sub SplitJudges() 
' Split down "Citing judge", column R.
' NB: alt+enter appears in columns G and S, so don't split every time alt+enter is found.
' For each n in cell R n: 
' Count alt + enter, set as x
' Put x new rows in 
' Copy data 
' Split contents of R(n) across R(n), R(n+1), ... R(n+x) 
' 
  Application.ScreenUpdating = False 
Dim sh As Worksheet 
Dim col As Integer ' says what column you're using (convert to int) 
col = 18 
 
    Set sh = ThisWorkbook.Sheets("Cases") 
 
    Dim length_t As Long 
 
    length_t = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count 
  
 Dim index As Integer 
 Dim row_n As Integer 
  For row_n = 2 To length_t 
    myString = Split(Cells(row_n, col), Chr(10)) 
    num_to_split = UBound(myString, 1) 
    If num_to_split > 0 Then 
        sh.Rows(num_to_split).Insert Shift:=xlShiftDown 
        For jLoop = 1 To num_to_split + 1 
            Dim c As Integer 
            c = row_n + jLoop - 1 
            sh.Range("A" & row_n & ":Q" & row_n).Copy sh.Range("A" & c & ":Q" & c) 
            sh.Range("S" & row_n & ":T" & row_n).Copy sh.Range("S" & c & ":T" & c) 
            sh.Cells(c, col) = myString(jLoop) 
        Next 
    Else 
    End If 
Next 
Application.ScreenUpdating = True 
End Sub

I'm sure I've done something stupid, because the code does something stupid, but I can't work out what it is... Any fresh pairs of eyes that can help? Thanks a million in advance.

Cheers,

cam394

 

[wideboydixon]

Your code seems a bit odd. Here's a commented new version that will hopefully work:

Public Sub SplitJudges2()

Dim lastRow As Long
Dim thisRow As Long
Dim casesSheet As Worksheet
Dim judges As Variant
Dim judge As Long

' Set up the worksheet
Set casesSheet = ThisWorkbook.Sheets("Cases")

' Find the last row in column A
lastRow = casesSheet.Cells(casesSheet.Rows.Count, 1).End(xlUp).Row

' Start on row 2
thisRow = 2

' Keep going until the end
Do While thisRow <= lastRow
    ' Create an array of judges
    judges = Split(casesSheet.Cells(thisRow, 18).Value, Chr(10))
    
    ' Are there multiple judges?
    If UBound(judges) > 0 Then
        ' Insert a new row for each extra judge and copy the data from this row
        For judge = 1 To UBound(judges)
            casesSheet.Rows(thisRow + 1).Insert Shift:=xlShiftDown
            casesSheet.Rows(thisRow).Copy Destination:=casesSheet.Cells(thisRow + 1, 1)
        Next judge
        
        ' Now put on judge on each line
        For judge = 0 To UBound(judges)
            casesSheet.Cells(thisRow + judge, 18).Value = judges(judge)
        Next judge
        
        ' We've just shifted the last row down - make the adjustment here
        lastRow = lastRow + UBound(judges)
    End If
    
    ' Move down to the next row
    thisRow = thisRow + UBound(judges) + 1
Loop

End Sub

WBD