Macro for deleting cell value in excelSolved/Closed

Question

Hi,

We have a requirement to delete value in specific set of cells in excel using VBA script.

Eg: I need to delete d3:d4,e3:e4,g3:g4,h3:h4
and another set d9:d11,e9:e11,g9:g11,h9:h11.

sub delete 

I am trying this way 
sheets("sheet1").range("d3:e4).select
selection.clearcontents
sheets("sheet1").range("g3:h4).select
selection.clearcontents
.
.
.
.

end sub

But this way redundancy is seen. Can some one please help me in writing a better code.

Thanks,
Mouni873

rizvisa1 · Accepted Answer

Create a common routine and call that routine


'rowToClearStarting : first row in the range that needs to be cleared
'rowToClearEnding   : last row in the range that needs to be cleared
'Sample Call        : clearContents(4,6)
Public Sub clearContents(rowToClearStarting As Long, rowToClearEnding As Long)

   Sheets("sheet1").Range("d" & rowToClearStarting & ":d" & rowToClearEnding).clearContents
   Sheets("sheet1").Range("e" & rowToClearStarting & ":e" & rowToClearEnding).clearContents
   Sheets("sheet1").Range("g" & rowToClearStarting & ":g" & rowToClearEnding).clearContents
   Sheets("sheet1").Range("h" & rowToClearStarting & ":h" & rowToClearEnding).clearContents

End Sub

Mounika · Answer

Hi Rizvisa1, 

Thanks for the reply.
Even this seems to be redundant... can't we specify the entire cells range and clear everything with single clearContents?

Just like range function which selects only one particular cell range, dont we have any other function.
Eg: A1:A5,B2:b8 both the ranges should be given in one particular function and we should be able to delete it . 
Eg : sheets("sheet1").<function>("A1:A5","B2:B8").clearContents

Thanks,
Mouni873

Mounika · Answer

Thanks rizvisa1. This is what exactly I am looking for.

Macro for deleting cell value in excel

3 replies

Subscribe To Our Newsletter!