Back to Excel VBA archive index

Back to archive home

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 | ||||||

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.

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.

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

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

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

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

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

with correct code..that if not bother u..thanks for

your time

John,

Here is the correct syntax. Hope it takes care of what you need, let me know!

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

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.

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.