Macro for deleting cell value in excel [Solved/Closed]

Mouni369 4 Posts Monday March 12, 2012Registration date July 20, 2012 Last seen - Mar 12, 2012 at 01:18 AM - Latest reply:  Masbrow
- Feb 11, 2016 at 10:58 PM
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
See more 

5 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 25, 2012 at 07:51 AM
2
Thank you
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

Thank you, rizvisa1 2

Something to say? Add comment

CCM has helped 1692 users this month

1
Thank you
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
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 25, 2012 at 10:21 PM
Depends on what you want to do
One way would be

union(range("a1:c4"),range("b4:b6"),range("h4:h5")).clearcontents
How to delete combined range and cell. E.g. I want to delete C6:E20 and G3.
0
Thank you
Thanks rizvisa1. This is what exactly I am looking for.