xcellrodeo
Board Regular
- Joined
- Oct 27, 2008
- Messages
- 209
Hi there, I have a set of data (example below) which runs from Col.B->AM. Number of rows varies.
What I would like to achieve is to have a VBA script that searches
through the columns starting from Col.E and ending in Col.AM to remove any spaces.
However this should only be done in cells that have no visible content so as to preserve any cells with content where the spaces are legitimate. The number of rows will depend on the size of the data which is variable.But for this, a count function could be added. Please note that empty columns between Col.E & AM can vary as below example only shows Col I & J as having no visible content.
Any help would be appreciated.Thanks
[TABLE="width: 668"]
<tbody>[TR]
[TD="width: 37, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: #E5E0EC"]A[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]B[/TD]
[TD="width: 87, bgcolor: #E5E0EC"]C[/TD]
[TD="width: 83, bgcolor: #E5E0EC"]D[/TD]
[TD="width: 85, bgcolor: #E5E0EC"]E[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]F[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]G[/TD]
[TD="width: 86, bgcolor: #E5E0EC"]H[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]I[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]J[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]K[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]L[/TD]
[/TR]
[TR]
[TD="bgcolor: #E5E0EC"]2[/TD]
[TD="bgcolor: transparent"]P/N[/TD]
[TD="bgcolor: transparent"]COUNT[/TD]
[TD="bgcolor: transparent"]COMPLETE[/TD]
[TD="bgcolor: transparent"]SUPPL[/TD]
[TD="bgcolor: transparent"]CO1[/TD]
[TD="bgcolor: transparent"]CO2[/TD]
[TD="bgcolor: transparent"]CO3[/TD]
[TD="bgcolor: transparent"]CO4[/TD]
[TD="bgcolor: transparent"]CO5[/TD]
[TD="bgcolor: transparent"]CO6[/TD]
[TD="bgcolor: transparent"]CO7[/TD]
[TD="bgcolor: transparent"]CO8[/TD]
[/TR]
[TR]
[TD="bgcolor: #E5E0EC"]3[/TD]
[TD="bgcolor: transparent"]K550[/TD]
[TD="bgcolor: transparent"]6
[/TD]
[TD="bgcolor: transparent"]ONE LINE[/TD]
[TD="bgcolor: transparent"]90893R 200[/TD]
[TD="bgcolor: transparent"]TEC[/TD]
[TD="bgcolor: transparent"]R-48245[/TD]
[TD="bgcolor: transparent"]COR[/TD]
[TD="bgcolor: transparent"]OP-09 0223[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]TRI[/TD]
[TD="bgcolor: transparent"]9823 454[/TD]
[/TR]
</tbody>[/TABLE]
What I would like to achieve is to have a VBA script that searches
through the columns starting from Col.E and ending in Col.AM to remove any spaces.
However this should only be done in cells that have no visible content so as to preserve any cells with content where the spaces are legitimate. The number of rows will depend on the size of the data which is variable.But for this, a count function could be added. Please note that empty columns between Col.E & AM can vary as below example only shows Col I & J as having no visible content.
Any help would be appreciated.Thanks
[TABLE="width: 668"]
<tbody>[TR]
[TD="width: 37, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: #E5E0EC"]A[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]B[/TD]
[TD="width: 87, bgcolor: #E5E0EC"]C[/TD]
[TD="width: 83, bgcolor: #E5E0EC"]D[/TD]
[TD="width: 85, bgcolor: #E5E0EC"]E[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]F[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]G[/TD]
[TD="width: 86, bgcolor: #E5E0EC"]H[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]I[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]J[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]K[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]L[/TD]
[/TR]
[TR]
[TD="bgcolor: #E5E0EC"]2[/TD]
[TD="bgcolor: transparent"]P/N[/TD]
[TD="bgcolor: transparent"]COUNT[/TD]
[TD="bgcolor: transparent"]COMPLETE[/TD]
[TD="bgcolor: transparent"]SUPPL[/TD]
[TD="bgcolor: transparent"]CO1[/TD]
[TD="bgcolor: transparent"]CO2[/TD]
[TD="bgcolor: transparent"]CO3[/TD]
[TD="bgcolor: transparent"]CO4[/TD]
[TD="bgcolor: transparent"]CO5[/TD]
[TD="bgcolor: transparent"]CO6[/TD]
[TD="bgcolor: transparent"]CO7[/TD]
[TD="bgcolor: transparent"]CO8[/TD]
[/TR]
[TR]
[TD="bgcolor: #E5E0EC"]3[/TD]
[TD="bgcolor: transparent"]K550[/TD]
[TD="bgcolor: transparent"]6
[/TD]
[TD="bgcolor: transparent"]ONE LINE[/TD]
[TD="bgcolor: transparent"]90893R 200[/TD]
[TD="bgcolor: transparent"]TEC[/TD]
[TD="bgcolor: transparent"]R-48245[/TD]
[TD="bgcolor: transparent"]COR[/TD]
[TD="bgcolor: transparent"]OP-09 0223[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]TRI[/TD]
[TD="bgcolor: transparent"]9823 454[/TD]
[/TR]
</tbody>[/TABLE]
Last edited: