Macro to clear cells

**joe87** · 02-28-2011, 06:54 AM

Hi, I'm having trouble writing a macro to clear cells.

I want to clear the contents of a column when I switch between two values in a drop-down data validation menu. I have tried using the macro recorder but have had no success.

I'm new to macros!

Cheers

Joe

**Andy Pope** · 02-28-2011, 06:57 AM

Columns(3).clear

or

Range("H:H").clear

If you need more help you are going to have to supply more meaningful details.

**davesexcel** · 02-28-2011, 06:57 AM

Supply more details please.

**joe87** · 02-28-2011, 07:15 AM

Does this help?

I can attach the whole spreadsheet but its a little incomprehensible!

**Andy Pope** · 02-28-2011, 07:19 AM

Not really.

Take a step back. Imagine you know nothing of the workbook or it's purpose or what the guy posting is doing or wants.

Now read your post and see if you can understand the problem being asked.

**joe87** · 02-28-2011, 07:32 AM

OK...

In the worksheet "Interface", in cell D12, I have a drop down menu with 2 options - "Imperial" and "Metric". Whenever I change between the two options I want the cells in the range E16:E40 to be cleared.

I have no idea how to go about this except that it might involve a Change Event triggering a Macro?

Thanks for your help guys!

**royUK** · 02-28-2011, 07:36 AM

What is the drop down menu? Is it a cell with Data validation?

**Andy Pope** · 02-28-2011, 07:37 AM

right click sheet tab and pick View Code.
Paste the following

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Range("D12"), Target) Is Nothing Then
        Application.EnableEvents = False
        Range("E16:E40").Clear
        Application.EnableEvents = True
    End If
    
End Sub

**joe87** · 02-28-2011, 07:58 AM

Roy, It was a data validation cell.

Andy, that worked ok, but it has deleted the drop down menu options. Is there a way to just clear the contents of the cell without deleting the data validation facility?

Cheers

Joe

**Andy Pope** · 02-28-2011, 08:00 AM

        Range("E16:E40").ClearContents

**joe87** · 02-28-2011, 08:19 AM

Thank you so much! Fantastic

**aks_cool** · 04-23-2012, 10:21 PM

Hi,

Sorry for hijacking the thread.

I also have a cells C30 : C79 which have data validataion drop down values. On the basis of what being the input in the respective cells C30:C79 there is a dependent drop down values in the D30:D79. Again there is a drop down(YES|NO) in cells E30:E79.

Now my query is if i change the input in any of the cells in C30:C79 then respective cells under D30:D79 & E30:E79 should clear the contents so that the appropriate values can be selcted from the drop downs.

I have been trying to use below macro code however it doesnt solve the pupose. If I replace C30:C79 by C30, D30:D79 by D30 and E30:E79 by E30 then it works like a charm for the cells C30 but when i tried to generalize this for whole range it doesnt yield the result.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, ActiveSheet.Range("C30:C79")) Is Nothing Then Exit Sub
ActiveSheet.Range("D30:D79").Value = ""
ActiveSheet.Range("E30:E79").Value = ""
End Sub

Please let me know your inputs.

Thanks,
Aks