Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

please. i need macro to find blank cell and delete it

Posted by John on June 30, 2000 12:47 AM
i have a data from A1:B5

in range A1:A5, i type the student names

in range B1:B5 i'm using index match and when
i put names in A1:A5, the marks will shown
in range B1:B5

let say i have only 3 students
so range B4:B5 will shown N/A value
what i'm trying to do is when there is
a blank cell in range A1:A5, the formula
in range B1:B5 will be delete automatically
or just by pushing command button
lets say cell A5 is empty, so i need the
formula in cell B5 to be delete automatically
thanks


Check out our Excel VBA Resources

Re: please. i need macro to find blank cell and delete it

Posted by david on June 30, 0100 1:45 AM
So if a5 is empty to want to delete row 5 and shift all cells below that up right?

Sub Delete_Row()

temp = Range("A65536").End(xlUp).Row

For x = 1 To temp
Cells(x, 1).Select
If Cells(x, 1) = "" Or Cells(x, 1) = 0 Then
Rows(x).Select
Selection.Delete Shift:=xlUp
End If
Next x
End Sub

Simple enough.


Create a button and link it to it if you want. If i interpreted wrong then give more info.


another Option

Posted by david on June 30, 0100 1:50 AM

I reread your question and decided if you don't want to delete the row but also don't want an error you can do this

In front of your formula type if(iserr(insert entire formula),"",inset entire formula again)


so if a formula was x=78/0
you would type if(iserr(x=78/0),"",x=78/0)
It beats deleting things a lot of times the proble is with index match it is hard to insert maybe you want a macro to input this instead. It wouldn't be hard let us know.


thanks david but.....

Posted by John on June 30, 0100 3:39 AM
your code is when the cell is empty the whole
rows will be deleted..am i right..thanks
brother but i just want when it detect in
column A empty, it just delete the formula
in column B..is it possible thanks again

Re: thanks david but.....

Posted by david on June 30, 0100 6:06 AM
Sub Delete_Formula()
temp = Range("A65536").End(xlUp).Row
For x = 1 To temp
Cells(x, 1).Select
If Cells(x, 1) = "" Or Cells(x, 1) = 0 Then
cells(x,2)="" 'This line might not be correct. I do not have excel where i am so I could Not test it.
End If
Next x
End Sub

you right it's not working..inside..

Posted by John on June 30, 0100 6:28 AM
if you have spare time why don't u drop me an emai
with correct code..that if not bother u..thanks for
your time

Re: you right it's not working..inside..

Posted by Ryan on July 02, 0100 7:31 AM
John,
Here is the correct syntax. Hope it takes care of what you need, let me know!

RYan

Sub Delete_Formula()
temp = Range("A65536").End(xlUp).Row
For x = 1 To temp
Cells(x, 1).Select
If Cells(x, 1) = "" Or Cells(x, 1) = 0 Then
Cells(x, 2).Clear
End If
Next x
End Sub


Re: you right it's not working..inside..

Posted by david on July 02, 0100 7:42 PM
Thanks ryan hope that works for you john.


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.