cells not being delete

E

EDUCATED MONKEY

Board Regular
Joined
Jul 17, 2011
Messages
218
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p>
Can any one enlighten me as to why this does not work
</o:p><o:p>
Sub Find_Blank()<o:p></o:p>
' Find_Blank Macro<o:p></o:p>
' Keyboard Shortcut: Ctrl+Shift+F
<o:p> </o:p>
Dim x As Variant<o:p></o:p>
Dim i As Long<o:p></o:p>
<o:p> </o:p>
Application.ScreenUpdating = False<o:p></o:p>
<o:p> </o:p>
Worksheets("TEST").Activate<o:p></o:p>
<o:p> </o:p>
For i = 1 To 50<o:p></o:p>
Worksheets("TEST").Cells(i, 1).Activate<o:p></o:p>
x = ActiveCell.Value<o:p></o:p>
If x = "" Then<o:p></o:p>
ActiveCell.delete Shift:=xlUp<o:p></o:p>
End If<o:p></o:p>
Next i<o:p></o:p>
<o:p></o:p>
Application.ScreenUpdating = True<o:p></o:p>
<o:p> </o:p>
End Sub
</o:p>
<o:p></o:p>
<o:p></o:p>
Variable declaration are as if I was going to use bigger ranges later when it worked
<o:p></o:p>
A B columns <o:p></o:p>
<TABLE style="MARGIN: auto 6.75pt; WIDTH: 97.6pt; BORDER-COLLAPSE: collapse; mso-table-overlap: never; mso-table-lspace: 9.0pt; mso-table-rspace: 9.0pt; mso-table-anchor-vertical: paragraph; mso-table-anchor-horizontal: column; mso-table-left: left; mso-table-top: .05pt; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=130 align=left><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
1<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
1<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
2<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
1<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
3<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
4<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
5<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
<o:p></o:p>​
</TD></TR></TBODY></TABLE>
=IF(ISBLANK(A1),0,1) code in column B
=IF(ISBLANK(A2),0,1)

<o:p> Start Condition 1 above </o:p>
<TABLE style="MARGIN: auto 6.75pt; WIDTH: 97.6pt; BORDER-COLLAPSE: collapse; mso-table-lspace: 9.0pt; mso-table-rspace: 9.0pt; mso-table-anchor-vertical: paragraph; mso-table-anchor-horizontal: margin; mso-table-left: left; mso-table-top: 28.8pt; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=130 align=left><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
<o:p> </o:p>​
</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
0<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
0<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
3<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
4<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
5<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
<o:p></o:p>​
</TD></TR></TBODY></TABLE>
=IF(ISBLANK(A1),0,1)
=IF(ISBLANK(A2),0,1)<o:p></o:p>
<o:p></o:p><o:p></o:p>
<o:p> </o:p>
<o:p></o:p>
<o:p></o:p>
<o:p> Start Condition 2 cells A1,A2 manualy cleared and reported so
</o:p><o:p> </o:p>
<TABLE style="MARGIN: auto 6.75pt; WIDTH: 97.6pt; BORDER-COLLAPSE: collapse; mso-table-overlap: never; mso-table-lspace: 9.0pt; mso-table-rspace: 9.0pt; mso-table-anchor-vertical: paragraph; mso-table-anchor-horizontal: column; mso-table-left: left; mso-table-top: .05pt; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=130 align=left><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
0<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
3<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
1<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
4<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
5<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
6<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 48.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=65 noWrap>
<o:p></o:p>​
</TD></TR></TBODY></TABLE>
=IF(ISBLANK(A1),0,1)
=IF(ISBLANK(A2),0,1)<o:p></o:p>
<o:p> </o:p>
obviously I need to rest the reffs again thereport is correct
<o:p></o:p>
After sub runs A1 clear
<o:p> </o:p>
This is a simplified test I tested this of different numbers of cell the results were similar in that never did all the blank cells get shifted up ? <o:p></o:p>
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Forum Tip: Pasting VBA code in the forum editor
It would be best if you surround your VBA code with code tags e.g [CODE]your VBA code here[/CODE]
It makes reading your VBA code much easier.
When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign #.


When you delete a cell, the cell below it moves up to replace it. Then you test the next cell below. That means the cell that moved up never gets tested.

When deleting cells or rows, you need to loop from the bottom up to avoid skipping cells.

Code: 
For i = 50 to 1 Step -1
 
Upvote 0
AlphaFrog said:
Forum Tip: Pasting VBA code in the forum editor
It would be best if you surround your VBA code with code tags e.g [CODE]your VBA code here[/CODE]
It makes reading your VBA code much easier.
When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign #.


When you delete a cell, the cell below it moves up to replace it. Then you test the next cell below. That means the cell that moved up never gets tested.

When deleting cells or rows, you need to loop from the bottom up to avoid skipping cells.

Code: 
For i = 50 to 1 Step -1
Click to expand...
thanks for the info i will try to use the tags next time from what you said i now get it
the vba code would have been much easer if i had not studied boland c++ delph and java before hand, i find i am putting brackets round the wrong things then dots and even the -> has crept in and caused no end of problems, i am suprised how easy going things are dim's and tha like can be mised out and it still runs i know thats a bad idea other languages would play hell if you missed them out. thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
617
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
C
  • Question Question
Picture won't paste in cell overlay
Replies
0
Views
249
Cerka
C
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
920
epactheactor
E
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G

Forum statistics

Threads
1,224,518
Messages
6,179,254
Members
452,900
Latest member
LisaGo

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