VBA to shift cells to the left

[Katy Jordan]

Hi, how can i get this

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 76px"><COL style="WIDTH: 64px"><COL style="WIDTH: 197px"><COL style="WIDTH: 65px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 74px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>WTXMF3</TD><TD style="TEXT-ALIGN: right">1326154</TD><TD>RIO TINTO</TD><TD>AUD</TD><TD>MLWSINBF</TD><TD>SALE </TD><TD style="TEXT-ALIGN: right">258</TD><TD>414.13 ML W/S INT BD FD </TD><TD style="TEXT-ALIGN: right">16-Mar-09</TD><TD style="TEXT-ALIGN: right">17-Mar-09</TD><TD style="TEXT-ALIGN: right">-251307.75</TD><TD>N</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

to this

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 76px"><COL style="WIDTH: 64px"><COL style="WIDTH: 197px"><COL style="WIDTH: 65px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 74px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>WTXMF3</TD><TD style="TEXT-ALIGN: right">1326154</TD><TD>RIO TINTO</TD><TD>AUD</TD><TD>MLWSINBF</TD><TD>SALE </TD><TD>258 414.13 ML W/S INT BD FD </TD><TD style="TEXT-ALIGN: right">16-Mar-09</TD><TD style="TEXT-ALIGN: right">17-Mar-09</TD><TD style="TEXT-ALIGN: right">-251308</TD><TD>N</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[hiker95]

Katy Jordan,

That line did work:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
6	PFOLIO	A/C CODE	ACCT NAME	CUR	CODE	DESCRIPTN	NARRATIVE	DATE	ENTRY DATE	CASH VALUE	De
7	------	---------	--------------------------	----	----------	-----------	-----------------------------	------------	-------------	-----------------	---
8	WTXMF3	131131	RIO TINTO	AUD	MLWSINBF	ADJUSTMEN	2	385.26 ML W/S INT BD FD	29-Dec-08	8-Jan-09		N
9	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Jan-09	27-Jan-09		N
10	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Jan-09	27-Jan-09	-572.4	N
11	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	23-Feb-09		N
12	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	23-Feb-09	-519.62	N
13	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	24-Feb-09		N
14	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	24-Feb-09	519.62	N
15	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	24-Feb-09		N
16	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	24-Feb-09	-689.22	N
17	WTXMF3	878787	RIO TINTO	AUD	MLWSINBF	SALE	490	001.26 ML W/S INT BD FD	16-Feb-09	17-Feb-09	-480838.24	N
18	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Mar-09	23-Mar-09		N
19	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Mar-09	23-Mar-09	-312.37	N
20	WTXMF3	4544	RIO TINTO	AUD	MLWSINBF	SALE	258	414.13 ML W/S INT BD FD	16-Mar-09	17-Mar-09	-251307.75	N
21	WTXMF3	131131	RIO TINTO	AUD	MLWSINBF	SALE	273	715.73 ML W/S INT BD FD	23-Mar-09	25-Mar-09	-267475.02	N
22	WTXMF3	131131	RIO TINTO	AUD	MLWSINBF	SALE	401	875.41 ML W/S INT BD FD	27-Mar-09	31-Mar-09	-392672.47	N
23	WTXMF3	131131	RIO TINTO	AUD	CAPWIT	CAPITAL	CAP WITHD ORD	30-Mar-09	31-Mar-09	108	N
24	WTXMF3	131131	RIO TINTO	AUD	CAPITL	CAPITAL	CAP WITHD ORD	30-Mar-09	31-Mar-09	-0.01	N
25	WTXMF3	131131	RIO TINTO	AUD	CAPWIT	CAPITAL	CAP WITHD ORD	30-Mar-09	31-Mar-09	0.01	N
26	WTXMF3	12121	RIO TINTO	AUD	INT0005	SALE	39	727.08 INTECH BAL GR FD C	29-Dec-08	2-Jan-09	-27690.6	N
Sheet1

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
6	PFOLIO	A/C CODE	ACCT NAME	CUR	CODE	DESCRIPTN	NARRATIVE	DATE	ENTRY DATE	CASH VALUE	De
7	------	---------	--------------------------	----	----------	-----------	-----------------------------	------------	-------------	-----------------	---
8	WTXMF3	131131	RIO TINTO	AUD	MLWSINBF	ADJUSTMEN	2 385.26 ML W/S INT BD FD	29-Dec-08	8-Jan-09		N
9	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Jan-09	27-Jan-09		N
10	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Jan-09	27-Jan-09	-572.4	N
11	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	23-Feb-09		N
12	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	23-Feb-09	-519.62	N
13	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	24-Feb-09		N
14	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	24-Feb-09	519.62	N
15	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	24-Feb-09		N
16	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	24-Feb-09	-689.22	N
17	WTXMF3	878787	RIO TINTO	AUD	MLWSINBF	SALE	490 001.26 ML W/S INT BD FD	16-Feb-09	17-Feb-09	-480838.24	N
18	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Mar-09	23-Mar-09		N
19	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Mar-09	23-Mar-09	-312.37	N
20	WTXMF3	4544	RIO TINTO	AUD	MLWSINBF	SALE	258 414.13 ML W/S INT BD FD	16-Mar-09	17-Mar-09	-251307.75	N
21	WTXMF3	131131	RIO TINTO	AUD	MLWSINBF	SALE	273 715.73 ML W/S INT BD FD	23-Mar-09	25-Mar-09	-267475.02	N
22	WTXMF3	131131	RIO TINTO	AUD	MLWSINBF	SALE	401 875.41 ML W/S INT BD FD	27-Mar-09	31-Mar-09	-392672.47	N
23	WTXMF3	131131	RIO TINTO	AUD	CAPWIT	CAPITAL	CAP WITHD ORD	30-Mar-09	31-Mar-09	108	N
24	WTXMF3	131131	RIO TINTO	AUD	CAPITL	CAPITAL	CAP WITHD ORD	30-Mar-09	31-Mar-09	-0.01	N
25	WTXMF3	131131	RIO TINTO	AUD	CAPWIT	CAPITAL	CAP WITHD ORD	30-Mar-09	31-Mar-09	0.01	N
26	WTXMF3	12121	RIO TINTO	AUD	INT0005	SALE	39 727.08 INTECH BAL GR FD C	29-Dec-08	2-Jan-09	-27690.6	N
Sheet1

 

[Katy Jordan]

Hey Hiker, your code works but i have to run it twice for all the data be shifted left, how can we get around this?

Katy Jordan,

That line did work:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
6	PFOLIO	A/C CODE	ACCT NAME	CUR	CODE	DESCRIPTN	NARRATIVE	DATE	ENTRY DATE	CASH VALUE	De
7	------	---------	--------------------------	----	----------	-----------	-----------------------------	------------	-------------	-----------------	---
8	WTXMF3	131131	RIO TINTO	AUD	MLWSINBF	ADJUSTMEN	2	385.26 ML W/S INT BD FD	29-Dec-08	8-Jan-09		N
9	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Jan-09	27-Jan-09		N
10	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Jan-09	27-Jan-09	-572.4	N
11	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	23-Feb-09		N
12	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	23-Feb-09	-519.62	N
13	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	24-Feb-09		N
14	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	24-Feb-09	519.62	N
15	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	24-Feb-09		N
16	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	24-Feb-09	-689.22	N
17	WTXMF3	878787	RIO TINTO	AUD	MLWSINBF	SALE	490	001.26 ML W/S INT BD FD	16-Feb-09	17-Feb-09	-480838.24	N
18	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Mar-09	23-Mar-09		N
19	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Mar-09	23-Mar-09	-312.37	N
20	WTXMF3	4544	RIO TINTO	AUD	MLWSINBF	SALE	258	414.13 ML W/S INT BD FD	16-Mar-09	17-Mar-09	-251307.75	N
21	WTXMF3	131131	RIO TINTO	AUD	MLWSINBF	SALE	273	715.73 ML W/S INT BD FD	23-Mar-09	25-Mar-09	-267475.02	N
22	WTXMF3	131131	RIO TINTO	AUD	MLWSINBF	SALE	401	875.41 ML W/S INT BD FD	27-Mar-09	31-Mar-09	-392672.47	N
23	WTXMF3	131131	RIO TINTO	AUD	CAPWIT	CAPITAL	CAP WITHD ORD	30-Mar-09	31-Mar-09	108	N
24	WTXMF3	131131	RIO TINTO	AUD	CAPITL	CAPITAL	CAP WITHD ORD	30-Mar-09	31-Mar-09	-0.01	N
25	WTXMF3	131131	RIO TINTO	AUD	CAPWIT	CAPITAL	CAP WITHD ORD	30-Mar-09	31-Mar-09	0.01	N
26	WTXMF3	12121	RIO TINTO	AUD	INT0005	SALE	39	727.08 INTECH BAL GR FD C	29-Dec-08	2-Jan-09	-27690.6	N
Sheet1

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
6	PFOLIO	A/C CODE	ACCT NAME	CUR	CODE	DESCRIPTN	NARRATIVE	DATE	ENTRY DATE	CASH VALUE	De
7	------	---------	--------------------------	----	----------	-----------	-----------------------------	------------	-------------	-----------------	---
8	WTXMF3	131131	RIO TINTO	AUD	MLWSINBF	ADJUSTMEN	2 385.26 ML W/S INT BD FD	29-Dec-08	8-Jan-09		N
9	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Jan-09	27-Jan-09		N
10	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Jan-09	27-Jan-09	-572.4	N
11	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	23-Feb-09		N
12	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	23-Feb-09	-519.62	N
13	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	24-Feb-09		N
14	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	24-Feb-09	519.62	N
15	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	24-Feb-09		N
16	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Feb-09	24-Feb-09	-689.22	N
17	WTXMF3	878787	RIO TINTO	AUD	MLWSINBF	SALE	490 001.26 ML W/S INT BD FD	16-Feb-09	17-Feb-09	-480838.24	N
18	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Mar-09	23-Mar-09		N
19	WTXMF3	131131	RIO TINTO	AUD	AUDCUST	REINVEST	INTEREST RECEIVED	1-Mar-09	23-Mar-09	-312.37	N
20	WTXMF3	4544	RIO TINTO	AUD	MLWSINBF	SALE	258 414.13 ML W/S INT BD FD	16-Mar-09	17-Mar-09	-251307.75	N
21	WTXMF3	131131	RIO TINTO	AUD	MLWSINBF	SALE	273 715.73 ML W/S INT BD FD	23-Mar-09	25-Mar-09	-267475.02	N
22	WTXMF3	131131	RIO TINTO	AUD	MLWSINBF	SALE	401 875.41 ML W/S INT BD FD	27-Mar-09	31-Mar-09	-392672.47	N
23	WTXMF3	131131	RIO TINTO	AUD	CAPWIT	CAPITAL	CAP WITHD ORD	30-Mar-09	31-Mar-09	108	N
24	WTXMF3	131131	RIO TINTO	AUD	CAPITL	CAPITAL	CAP WITHD ORD	30-Mar-09	31-Mar-09	-0.01	N
25	WTXMF3	131131	RIO TINTO	AUD	CAPWIT	CAPITAL	CAP WITHD ORD	30-Mar-09	31-Mar-09	0.01	N
26	WTXMF3	12121	RIO TINTO	AUD	INT0005	SALE	39 727.08 INTECH BAL GR FD C	29-Dec-08	2-Jan-09	-27690.6	N
Sheet1

 

[hiker95]

Katy Jordan,

If you can send me your workbook for testing...

See my Private Message to you (top right hand corner of MrExcel, Welcome, Katy Jordan., "Your Notifications:".