how to delete a cell?

[mahaveer]

i have so many cells from column k1 to column N 100, which have formula but their result is "" (blank) therefore i want to delete all of them cells which have no value but how?

 

[nazmul_muneer]

Dear Mahabeer,

Select K1:N100

Press Ctrl+F

Click on Option>Select Values from Look in Combo>Find All>Press Ctrl+A

Close Find and Replace Dialog box

Now press Delete key


Done!


REGARDS,

Muneer

 

[sdsurzh]

Hi,


Press F5 and select special then slect formulas then uncheck numbers,logicals and errors.


Thanks,

Suresh Kumar S

 

[mahaveer]

thanks to all but i want to delete cell not cell value


and can u provide me a vb code or macro for this assuming my data is on sheet1.

 

[sdsurzh]

Hi,


Can you please create a new module and paste the below code and run (it will delete the entire row)


Sub delblankcell()

On Error GoTo test1

Selection.SpecialCells(xlCellTypeFormulas, 2).Select

Selection.EntireRow.Delete

Range("a1").Select

test1:

MsgBox "There is not blank cell"

End Sub


Thanks,

Suresh Kumar S

 

[mahaveer]

thanks suresh kumar ji

but i need only cell deletable vba code not entire row

becoz i want to delete only the certain cell which has no value but have formula.....

 

[sdsurzh]

Hi,


Can you please try the below code


Sub delblankcell()

On Error GoTo test1

Selection.SpecialCells(xlCellTypeFormulas, 2).Select

Selection.ClearContents

Range("a1").Select

test1:

MsgBox "There is not blank cell"

End Sub


Thanks,

Suresh Kumar S

 

[mahaveer]

NO DEAR

ITS REALLY DO NOT WORKING

ITS DELETING ALL THE CELLS FROM COLUMN "A".

 

[mahaveer]

Thanks to all


i did it my self..............


but thanks to help

 

[bobhc]

Good day mahaveer


Could you be so kind as to tell the forum how you did it others may be interested

 

[mahaveer]

HI NAZMUL_MUNEER


CAN U PROVIDE ME VBA CODE FOR THE SAME WHICH U TOLD ME.....AS follows:

Select sheet 1

Select K1:N100

Press Ctrl+F

Click on Option>Select Values from Look in Combo>Find All>Press Ctrl+A

Close Find and Replace Dialog box

Now press Delete key

 

[nazmul_muneer]

Dear Mahaveer,

You can try the code


Range("K1:N100").Select

Selection.SpecialCells(xlCellTypeFormulas, 2).Select

Selection.ClearContents

Range("K1").Select


Regards,


Muneer

 

[nazmul_muneer]

Dear Mahaveer,

The steps i provided on 08/Nov/2012, it is not possible to solve through macro because you can not use find function in macro.

So you can use the latest code. It also works same.


Muneer

 

[mahaveer]

hi nazmul.......

thanks

but it deletes my all formula from column k to N

but i want to delete only those cells which haves formulas but have no value

i dont want to delete all the cells having formulas

becoz there are some cells having formula and value too.....and some cells having formulas but no value.....and i want to delete those cells having formulas but no value.......

 

[kaushik03]

Mahaveer,


Did you try this?


Sub Deletes()


Dim rng As Range

Set rng = ActiveSheet.UsedRange


' Check each cell in the range and if its value is empty, delete the row

For Each Cell In rng

If Cell.Value = "" Then

Cell.EntireRow.Delete

End If

Next Cell


End Sub

 

[shrivallabha]

Nazmul Muneer's idea can be loosely implemented like this:

[pre]

Public Sub NMCode()
Dim strFirst As String
Dim rngFirst As Range
Application.ScreenUpdating = False
With Range("K1:N100")
Set rngFirst = .Find(vbNullString, [K1], xlValues, xlWhole, xlByRows, xlNext, False)
strFirst = rngFirst.Address
Do
rngFirst.Value = vbNullString
Set rngFirst = .FindNext(rngFirst)
Loop While rngFirst.Address <> strFirst
End With
Application.ScreenUpdating = True
End Sub

[/pre]