shift cells to the left

M

marc005

Board Regular
Joined
Apr 21, 2013
Messages
58
My worksheet looks like this:

A
7 Days Group Holdings
A
Aaron's Inc.
ABB ADS
Abbott Laboratories
Advanced Micro Devices
Advanced Semiconductor Engineering ADS
Advantage Oil&Gas
Advantest Corp ADS
AECOM Technology
Aegean Marine Petroleum Network
Aegon NV ADS
AerCap Holdings
Aeroflex Holding
A
ChgHighLow%Chg
AeropostaleARO......
AESAES... dd
AetnaAET

<tbody>
[TD="class: xl65, width: 68"] B [/TD]
[TD="class: xl65, width: 49"] C [/TD]
[TD="class: xl65, width: 49"] D[/TD]
[TD="class: xl65, width: 70"]E[/TD]
[TD="class: xl65, width: 49"] F[/TD]
[TD="class: xl65, width: 55"] G[/TD]
[TD="class: xl65, width: 47"] H[/TD]
[TD="class: xl66, width: 71"] I[/TD]
[TD="class: xl65, width: 71"] J[/TD]
[TD="class: xl65, width: 70"] K[/TD]
[TD="class: xl65, width: 49"] L[/TD]
[TD="class: xl65, width: 42"] M[/TD]
[TD="class: xl65, width: 56"]N[/TD]
[TD="class: xl65, width: 49"] O[/TD]
[TD="class: xl65, width: 49"] P[/TD]

[TD="class: xl65"]SVN[/TD]
[TD="class: xl65"]13.59[/TD]
[TD="class: xl65"]13.59[/TD]
[TD="class: xl65"]13.5[/TD]
[TD="class: xl65"]13.54[/TD]
[TD="class: xl65"]-0.05[/TD]
[TD="class: xl65"]-0.37[/TD]
[TD="class: xl66"]57,657[/TD]
[TD="class: xl65"]13.6[/TD]
[TD="class: xl65"]8.1[/TD]
[TD="class: xl65"]...[/TD]
[TD="class: xl65"]...[/TD]
[TD="class: xl65"]23.93[/TD]
[TD="class: xl65"]12.27[/TD]
[TD="class: xl65"][/TD]

[TD="colspan: 9"]0-9 | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]Symbol[/TD]
[TD="class: xl65"]Open[/TD]
[TD="class: xl65"]High[/TD]
[TD="class: xl65"]Low[/TD]
[TD="class: xl65"]Close[/TD]
[TD="class: xl65"]Net[/TD]
[TD="class: xl65"]%Chg[/TD]
[TD="class: xl65"]Vol[/TD]
[TD="class: xl65"]52 Week[/TD]
[TD="class: xl65"]52 Week[/TD]
[TD="class: xl65"]Div[/TD]
[TD="class: xl65"]Yield[/TD]
[TD="class: xl65"]PE[/TD]
[TD="class: xl65"]YTD[/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Chg[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]High[/TD]
[TD="class: xl65"]Low[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]%Chg[/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]AAR Corp.[/TD]
[TD="class: xl65"]AIR[/TD]
[TD="class: xl65"]16.56[/TD]
[TD="class: xl65"]16.62[/TD]
[TD="class: xl65"]16.02[/TD]
[TD="class: xl65"]16.52[/TD]
[TD="class: xl65"]...[/TD]
[TD="class: xl65"]...[/TD]
[TD="class: xl66"]343,435[/TD]
[TD="class: xl65"]20.44[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]0.3[/TD]
[TD="class: xl65"]1.82[/TD]
[TD="class: xl65"]9.72[/TD]
[TD="class: xl65"]-11.56[/TD]

[TD="class: xl65"]AAN[/TD]
[TD="class: xl65"]28.29[/TD]
[TD="class: xl65"]28.6[/TD]
[TD="class: xl65"]28.03[/TD]
[TD="class: xl65"]28.35[/TD]
[TD="class: xl65"]-0.18[/TD]
[TD="class: xl65"]-0.63[/TD]
[TD="class: xl66"]269,243[/TD]
[TD="class: xl65"]32.53[/TD]
[TD="class: xl65"]24.61[/TD]
[TD="class: xl65"]0.07[/TD]
[TD="class: xl65"]0.24[/TD]
[TD="class: xl65"]12.42[/TD]
[TD="class: xl65"]0.25[/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]ABB[/TD]
[TD="class: xl65"]21.37[/TD]
[TD="class: xl65"]21.42[/TD]
[TD="class: xl65"]21.01[/TD]
[TD="class: xl65"]21.21[/TD]
[TD="class: xl65"]-0.21[/TD]
[TD="class: xl65"]-0.98[/TD]
[TD="class: xl66"]2,130,306[/TD]
[TD="class: xl65"]23.37[/TD]
[TD="class: xl65"]15.26[/TD]
[TD="class: xl65"]0.74[/TD]
[TD="class: xl65"]3.48[/TD]
[TD="class: xl65"]18.5[/TD]
[TD="class: xl65"]2.02[/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]ABT[/TD]
[TD="class: xl65"]37[/TD]
[TD="class: xl65"]37.22[/TD]
[TD="class: xl65"]36.56[/TD]
[TD="class: xl65"]36.89[/TD]
[TD="class: xl65"]-0.31[/TD]
[TD="class: xl65"]-0.83[/TD]
[TD="class: xl66"]5,301,339[/TD]
[TD="class: xl65"]37.55[/TD]
[TD="class: xl65"]28.4[/TD]
[TD="class: xl65"]0.56[/TD]
[TD="class: xl65"]1.52[/TD]
[TD="class: xl65"]11.15[/TD]
[TD="class: xl65"]17.71[/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]AMD[/TD]
[TD="class: xl65"]2.47[/TD]
[TD="class: xl65"]2.51[/TD]
[TD="class: xl65"]2.43[/TD]
[TD="class: xl65"]2.46[/TD]
[TD="class: xl65"]-0.01[/TD]
[TD="class: xl65"]-0.4[/TD]
[TD="class: xl66"]14,009,379[/TD]
[TD="class: xl65"]7.72[/TD]
[TD="class: xl65"]1.81[/TD]
[TD="class: xl65"]...[/TD]
[TD="class: xl65"]...[/TD]
[TD="class: xl65"]... dd[/TD]
[TD="class: xl65"]2.5[/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]ASX[/TD]
[TD="class: xl65"]4.3[/TD]
[TD="class: xl65"]4.3[/TD]
[TD="class: xl65"]4.25[/TD]
[TD="class: xl65"]4.3[/TD]
[TD="class: xl65"]0.02[/TD]
[TD="class: xl65"]0.47[/TD]
[TD="class: xl66"]329,354[/TD]
[TD="class: xl65"]4.65[/TD]
[TD="class: xl65"]3.14[/TD]
[TD="class: xl65"]0.11[/TD]
[TD="class: xl65"]2.57[/TD]
[TD="class: xl65"]14.81[/TD]
[TD="class: xl65"]0.47[/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]AAV[/TD]
[TD="class: xl65"]3.71[/TD]
[TD="class: xl65"]3.74[/TD]
[TD="class: xl65"]3.63[/TD]
[TD="class: xl65"]3.65[/TD]
[TD="class: xl65"]-0.06[/TD]
[TD="class: xl65"]-1.62[/TD]
[TD="class: xl66"]110,525[/TD]
[TD="class: xl65"]4.2[/TD]
[TD="class: xl65"]2.47[/TD]
[TD="class: xl65"]...[/TD]
[TD="class: xl65"]...[/TD]
[TD="class: xl65"]... dd[/TD]
[TD="class: xl65"]12.65[/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]ATE[/TD]
[TD="class: xl65"]13.94[/TD]
[TD="class: xl65"]14.04[/TD]
[TD="class: xl65"]13.86[/TD]
[TD="class: xl65"]13.94[/TD]
[TD="class: xl65"]-0.25[/TD]
[TD="class: xl65"]-1.76[/TD]
[TD="class: xl66"]9,357[/TD]
[TD="class: xl65"]17.25[/TD]
[TD="class: xl65"]10.8[/TD]
[TD="class: xl65"]...[/TD]
[TD="class: xl65"]...[/TD]
[TD="class: xl65"]51.1[/TD]
[TD="class: xl65"]-13.52[/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]ACM[/TD]
[TD="class: xl65"]28.38[/TD]
[TD="class: xl65"]28.86[/TD]
[TD="class: xl65"]27.71[/TD]
[TD="class: xl65"]28.59[/TD]
[TD="class: xl65"]0.37[/TD]
[TD="class: xl65"]1.31[/TD]
[TD="class: xl66"]838,632[/TD]
[TD="class: xl65"]33.09[/TD]
[TD="class: xl65"]14.5[/TD]
[TD="class: xl65"]...[/TD]
[TD="class: xl65"]...[/TD]
[TD="class: xl65"]... dd[/TD]
[TD="class: xl65"]20.13[/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]ANW[/TD]
[TD="class: xl65"]5.87[/TD]
[TD="class: xl65"]5.96[/TD]
[TD="class: xl65"]5.74[/TD]
[TD="class: xl65"]5.91[/TD]
[TD="class: xl65"]...[/TD]
[TD="class: xl65"]...[/TD]
[TD="class: xl66"]63,241[/TD]
[TD="class: xl65"]7.88[/TD]
[TD="class: xl65"]4.28[/TD]
[TD="class: xl65"]0.04[/TD]
[TD="class: xl65"]0.68[/TD]
[TD="class: xl65"]13.74[/TD]
[TD="class: xl65"]11.93[/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]AEG[/TD]
[TD="class: xl65"]5.91[/TD]
[TD="class: xl65"]5.94[/TD]
[TD="class: xl65"]5.81[/TD]
[TD="class: xl65"]5.92[/TD]
[TD="class: xl65"]0.02[/TD]
[TD="class: xl65"]0.34[/TD]
[TD="class: xl66"]643,559[/TD]
[TD="class: xl65"]6.95[/TD]
[TD="class: xl65"]3.93[/TD]
[TD="class: xl65"]0.29[/TD]
[TD="class: xl65"]4.96[/TD]
[TD="class: xl65"]6.59[/TD]
[TD="class: xl65"]-8.07[/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]AER[/TD]
[TD="class: xl65"]15.3[/TD]
[TD="class: xl65"]15.4[/TD]
[TD="class: xl65"]15.17[/TD]
[TD="class: xl65"]15.29[/TD]
[TD="class: xl65"]...[/TD]
[TD="class: xl65"]...[/TD]
[TD="class: xl66"]361,516[/TD]
[TD="class: xl65"]16.57[/TD]
[TD="class: xl65"]10.6[/TD]
[TD="class: xl65"]...[/TD]
[TD="class: xl65"]...[/TD]
[TD="class: xl65"]12.74[/TD]
[TD="class: xl65"]11.44[/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]ARX[/TD]
[TD="class: xl65"]7.09[/TD]
[TD="class: xl65"]7.19[/TD]
[TD="class: xl65"]7.02[/TD]
[TD="class: xl65"]7.18[/TD]
[TD="class: xl65"]0.04[/TD]
[TD="class: xl65"]0.56[/TD]
[TD="class: xl66"]22,563[/TD]
[TD="class: xl65"]11.5[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]...[/TD]
[TD="class: xl65"]...[/TD]
[TD="class: xl65"]... dd[/TD]
[TD="class: xl65"]2.57[/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]Symbol[/TD]
[TD="class: xl65"]Open[/TD]
[TD="class: xl65"]High[/TD]
[TD="class: xl65"]Low[/TD]
[TD="class: xl65"]Close[/TD]
[TD="class: xl65"]Net[/TD]
[TD="class: xl65"]%Chg[/TD]
[TD="class: xl65"]Vol[/TD]
[TD="class: xl65"]52 Week[/TD]
[TD="class: xl65"]52 Week[/TD]
[TD="class: xl65"]Div[/TD]
[TD="class: xl65"]Yield[/TD]
[TD="class: xl65"]PE[/TD]
[TD="class: xl65"]YTD[/TD]
[TD="class: xl65"][/TD]

[TD="align: right"]13.05[/TD]
[TD="align: right"]13.22[/TD]
[TD="align: right"]12.64[/TD]
[TD="align: right"]13.04[/TD]
[TD="align: right"]-0.01[/TD]
[TD="align: right"]-0.08[/TD]
[TD="class: xl67, align: right"]1,551,799[/TD]
[TD="align: right"]23.05[/TD]
[TD="align: right"]11.76[/TD]

[TD="align: right"]30.26[/TD]
[TD="align: right"]0.23[/TD]

[TD="align: right"]12.88[/TD]
[TD="align: right"]13.01[/TD]
[TD="align: right"]12.7[/TD]
[TD="align: right"]12.92[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.39[/TD]
[TD="class: xl67, align: right"]3,836,382[/TD]
[TD="align: right"]13.2[/TD]
[TD="align: right"]9.52[/TD]
[TD="align: right"]0.16[/TD]
[TD="align: right"]1.24[/TD]

[TD="align: right"]20.75[/TD]

[TD="align: right"]55.88[/TD]
[TD="align: right"]55.88[/TD]
[TD="align: right"]54.88[/TD]
[TD="align: right"]55.02[/TD]
[TD="align: right"]-0.85[/TD]
[TD="align: right"]-1.52[/TD]
[TD="class: xl67, align: right"]1,909,309[/TD]
[TD="align: right"]56.92[/TD]
[TD="align: right"]34.58[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]1.45[/TD]
[TD="align: right"]11.32[/TD]
[TD="align: right"]18.81[/TD]

</tbody>


You can see that for some reason the rows that has a value in column P has been shifted one step to the right. How can I correct this?
Preferably I a loop that checks if cell(i,16) has a value then shift the row one step to the left. Also there are more rows than shown above.
 
Sort by date Sort by votes
Code: 
[color=darkblue]Sub[/color] P_Shift()
    [color=darkblue]Dim[/color] cell   [color=darkblue]As[/color] Range
    Application.ScreenUpdating = [color=darkblue]False[/color]
    [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] Range("P:P").SpecialCells(xlCellTypeConstants)
        Range("A" & cell.Row).Delete Shift:=xlShiftToLeft
    [color=darkblue]Next[/color] cell
    Application.ScreenUpdating = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
Thanx for your help AlphaFrog. The code works great :-)
You saved me a lot of trial and error time.
 
Upvote 0
You must log in or register to reply here.

Similar threads

1
  • Question Question
Calculate on every third Friday of the month and show results between positive & negative weeks
2
Replies
14
Views
1K
15minoffame
1
R
  • Question Question
lookup on calculated value not exact in list
Replies
5
Views
958
starkc69
S
G
  • Question Question
Add cell only if not hidden
Replies
3
Views
519
jasonb75
J

Forum statistics

Threads
1,226,840
Messages
6,193,282
Members
453,788
Latest member
drcharle

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top