Help needed to shift cells left if the cell to left of it is blank

[djpotter]

Hi all,

I need some help to solve a problem on an Excel 2007 spreadsheet.

Basically, I have 12 columns with 47571 rows in, and what I'd like to do is run a macro that will automatically check every cell to see if the cell to the left of it is blank, and if so, will move the data in that cell to the left to fill the blank cell.

So basically, it would tidy up the data in each row, eliminating all the blank cells while still keeping all the data in the same row. It doesn't matter that the data is shifting columns.

Any help would be greatly appreciated!

Thanks

David
djpotter@gmail DOT com

 

[Peter_SSs]

Welcome to the MrExcel board!

Test this on a copy of your workbook (or a smaller data set first).

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> MoveLeft()<br>    ActiveSheet.UsedRange.SpecialCells(xlBlanks).Delete Shift:=xlToLeft<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[djpotter]

Thanks for the suggestion - unfortunately, when I ran the macro it took a long time and then deleted ALL the data in the worksheet.

Am I doing something wrong?

I pressed Alt-F11 to get up the VBA environment.
Pasted in your code to the Thisworkbook part.

Then ran the code as macro in the worksheet after selecting all of the data.

Any ideas if I am the problem or the code?

I am almost a complete newbie to VBA etc...

THANK YOU

David

 

[Peter_SSs]

The code is not really in the right place, though that isn't the problem. When in the VB window, somewhere in your project (eg This Workbook where you put the code), use the menu to
Insert|Module
That is where you should put the code.

There is no need to select anything in the sheet before running the code.

So delete that previous code and try this code. Try it on a small data set first. I'm not sure why it would delete all your data but I didn't take enough notice of your data size and that you are using Excel 2007. There is an issue with Specialcells that has been improved in Excel 2010 which could be causing a problem for you.

Anyway, try this version.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> MoveLeft()<br>    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>      <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.UsedRange<br>        rws = .Rows.Count<br>        r = 1<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        <SPAN style="color:#00007F">Do</SPAN><br>            .Rows(r).Resize(8000).SpecialCells(xlBlanks).Delete Shift:=xlToLeft<br>            r = r + 8000<br>        <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> r <= rws<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
Clearly I don't know what your data is like but I did test this on 12 columns x 48,000 rows and it took less than 4 seconds.

 

[djpotter]

Hi,

Thanks for that. I thought I would post the first two rows to show the effect of the code you posted. It seems to be almost there but not quite, there are still some blank cells throughout the worksheet that aren't eliminated by that method. So table 1 is before the code is run and table 2 is after the code was run... any thoughts?

Table 1: Before the code is run

<TABLE style="WIDTH: 1205pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1606><COLGROUP><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 139pt; mso-width-source: userset; mso-width-alt: 6765" width=185><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" width=144><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5522" width=151><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 112pt; mso-width-source: userset; mso-width-alt: 5449" width=149><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5229" width=143><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><TBODY><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 32pt; HEIGHT: 15.95pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=42>ID</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 73pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=97>Company name</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 139pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=185>New Add 1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 108pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 width=144>New Add 2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 113pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 width=151>New Add 3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 51pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 width=68>New Add 4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 91pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 width=121>New Add 5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 86pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 width=115>New Add 6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 112pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=149>New Add 7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 107pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=143>New Add 8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 77pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=103>New Add 9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=79>New Add 10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 68pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=90>Post code</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 89pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=119>Country</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 32pt; HEIGHT: 15.95pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl66 height=21 width=42>1</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 73pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 width=97>"A" CERAMICS LIMITED</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">132 Manchester Road</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 112pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 width=149>Unit Shaw</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 107pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 width=143> </TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 width=103>Oldham</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 width=79>Lancashire</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 width=90>OL2 7DD</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 89pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 width=119>England</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 32pt; HEIGHT: 15.95pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl66 height=21 width=42>2</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 73pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 width=97>"RED BAND" CHEMICAL COMPANY, LIMITED</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">19 Smith's Place</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"> Midlothian</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 112pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 width=149>Midlothian</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 107pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 width=143>Edinburgh</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 width=103> </TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 width=79> </TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 width=90>EH6 8NT</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 89pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl67 width=119>Scotland</TD></TR></TBODY></TABLE>


Table 2: After the code is run...

<TABLE style="WIDTH: 1235pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1646><COLGROUP><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 139pt; mso-width-source: userset; mso-width-alt: 6765" width=185><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" width=144><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5522" width=151><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5668" width=155><COL style="WIDTH: 112pt; mso-width-source: userset; mso-width-alt: 5449" width=149><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5229" width=143><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><TBODY><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 32pt; HEIGHT: 15.95pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21 width=42>ID</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 73pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=97>Company name</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 139pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=185>New Add 1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 108pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=144>New Add 2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 113pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=151>New Add 3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 51pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=68>New Add 4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 91pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=121>New Add 5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 116pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=155>New Add 6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 112pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=149>New Add 7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 107pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=143>New Add 8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 77pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=103>New Add 9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=79>New Add 10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 68pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=90>Post code</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 89pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=119>Country</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 32pt; HEIGHT: 15.95pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl64 height=21 width=42>1</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 73pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=97>"A" CERAMICS LIMITED</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">132 Manchester Road</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 108pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=144>Unit Shaw</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 113pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=151>Oldham</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=68>Lancashire</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 91pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=121>OL2 7DD</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 116pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=155>England</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 32pt; HEIGHT: 15.95pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl64 height=21 width=42>2</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 73pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=97>"RED BAND" CHEMICAL COMPANY, LIMITED</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">19 Smith's Place</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"> Midlothian</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 113pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=151>Midlothian</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=68>Edinburgh</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 91pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=121> </TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl68> </TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 112pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=149>EH6 8NT</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 107pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=143>Scotland</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR></TBODY></TABLE>


Thanks!
David

 

[Peter_SSs]

My first thoughts are that those cells are actually not blank. In a couple of vacant cells somewhere try these formulas, replacing A1 with the address of one of these 'blank' cells that have not been deleted. What results do you get from that?

=LEN(A1)
=CODE(A1)

 

[djpotter]

Hi Peter,

I get...

=LEN(H3) ---> 0
=CODE(H3) --> #VALUE!

I tried it on a few other vacant cells and got the same.

Any thoughts?

Thank you!

David

 

[Peter_SSs]

Well that would indicate that that they are blank, dispelling my previous doubts.

How many columns of data are there (before the code is run)?

Try running the code again on the same data, but changing the two 8000 values to, say, 400.

 

[djpotter]

Hiya,

That produced this:
<TABLE style="WIDTH: 1255pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1673><COLGROUP><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 139pt; mso-width-source: userset; mso-width-alt: 6765" width=185><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" width=144><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5522" width=151><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 112pt; mso-width-source: userset; mso-width-alt: 5449" width=149><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5229" width=143><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><TBODY><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 32pt; HEIGHT: 15.95pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21 width=42>ID</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 73pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=97>Company name</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 139pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=185>New Add 1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 108pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=144>New Add 2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 113pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=151>New Add 3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 51pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=68>New Add 4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 91pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=121>New Add 5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 86pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=115>New Add 6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 112pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=149>New Add 7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 107pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=143>New Add 8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 77pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=103>New Add 9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=79>New Add 10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 68pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=90>Post code</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 89pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=119>Country</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 50pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=67></TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 32pt; HEIGHT: 15.95pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl64 height=21 width=42>1</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 73pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=97>"A" CERAMICS LIMITED</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">132 Manchester Road</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 108pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=144>Unit Shaw</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 113pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=151>Oldham</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=68>Lancashire</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 91pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=121>OL2 7DD</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 86pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=115>England</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>0</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 32pt; HEIGHT: 15.95pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl64 height=21 width=42>2</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 73pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=97>"RED BAND" CHEMICAL COMPANY, LIMITED</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">19 Smith's Place</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"> Midlothian</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 113pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=151>Midlothian</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=68>Edinburgh</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 91pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=121> </TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 86pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=115> </TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 112pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=149>EH6 8NT</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 107pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=143>Scotland</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=middle>#VALUE!</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR></TBODY></TABLE>

Any thoughts?

Thanks
David

 

[Peter_SSs]

Any thoughts?

Yes - that I made the (unfounded) assumption that you data was just text and did not contain formulas.
Is that the case for those 'empty' cells?
If so, ..
- The cells are not empty/blank but contain null strings ("") and therefore are not included in SpecialCells(xlBlanks)
- Do you want/need to retain any formulas in your sheet?

Also, I would still be interested in the answer to my previous question

How many columns of data are there (before the code is run)?