MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

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.

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

another Option

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.

Posted by John on June 30, 0100 3:39 AM

thanks david but.....

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

Posted by david on June 30, 0100 6:06 AM

Re: thanks david but.....

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

Posted by John on June 30, 0100 6:28 AM

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

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

Posted by Ryan on July 02, 0100 7:31 AM

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

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

Posted by david on July 02, 0100 7:42 PM

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

Thanks ryan hope that works for you john.