Excel - A macro to delete a particular cell value

August 2017



Issue


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

Solution


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


Thanks to Rrizvisa1 for this tip.

Related


Published by aakai1056. Latest update on May 28, 2012 at 12:22 PM by aakai1056.
This document, titled "Excel - A macro to delete a particular cell value," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).