Menu
  1. Accueil
  2. Tech Forum
  3. Programming

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

Report
Posts
4
Registration date
Monday March 12, 2012
Status
Member
Last seen
July 20, 2012
 -
 Masbrow -
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

3 replies

Answer 1 / 3
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 760
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
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Answer 2 / 3

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
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 760
Depends on what you want to do
One way would be

union(range("a1:c4"),range("b4:b6"),range("h4:h5")).clearcontents
Report

How to delete combined range and cell. E.g. I want to delete C6:E20 and G3.
Answer 3 / 3

Thanks rizvisa1. This is what exactly I am looking for.
Ask a question