Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically deleting data, using macros
i have some code on the bottom of one of my sheets, something like this:
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub Rows("6:47").EntireRow.Hidden = False If Range("B3") = "Standardized Module Width" Then Rows("6:6").EntireRow.Hidden = False : Rows("15:15").EntireRow.Hidden = False end if end if end sub : Im wondering if its possible to delete the information in cells, eg: F6, F15, ... when another option is selected - so not Standardized module width - i have 3 options... can anyone help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically deleting data, using macros
Do you mean...
Range("F6").Clearcontents Range("F15").Clearcontents If this post helps click Yes --------------- Jacob Skaria "Derrick" wrote: i have some code on the bottom of one of my sheets, something like this: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub Rows("6:47").EntireRow.Hidden = False If Range("B3") = "Standardized Module Width" Then Rows("6:6").EntireRow.Hidden = False : Rows("15:15").EntireRow.Hidden = False end if end if end sub : Im wondering if its possible to delete the information in cells, eg: F6, F15, ... when another option is selected - so not Standardized module width - i have 3 options... can anyone help? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically deleting data, using macros
You can continue using your If statement. If only one crtieria, you could
just use "Else", or you can nest more If statements if needed. To delete data, try: Range("F6").ClearContents Possible new macro: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub Rows("6:47").EntireRow.Hidden = False If Range("B3") = "Standardized Module Width" Then Rows("6:6").EntireRow.Hidden = False Rows("15:15").EntireRow.Hidden = False Else Range("F6").ClearContents end if end if end sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Derrick" wrote: i have some code on the bottom of one of my sheets, something like this: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub Rows("6:47").EntireRow.Hidden = False If Range("B3") = "Standardized Module Width" Then Rows("6:6").EntireRow.Hidden = False : Rows("15:15").EntireRow.Hidden = False end if end if end sub : Im wondering if its possible to delete the information in cells, eg: F6, F15, ... when another option is selected - so not Standardized module width - i have 3 options... can anyone help? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically deleting data, using macros
For just 3 conditions, you could use
IF Range("B3")= "condition 1" action ElseIf Range("B3") = "condition 2" action code Else 3rd 'catch all' action End If Or if you think you may eventually have more than 3 conditions, using Select Case could be a better option, as it's easier to modify later. The UCase(Trim( will remove white space at beginning/end of B3, and convert all to upper case for testing to take the worry out of case of value in B3. Select Case UCase(Trim(Range("B3"))) Case Is = "CONDITION 1" action code for condition 1 Case Is = "CONDITION 2" action code for condition 2 Case Is = "CONDITION 3" action code for condition 3 Case Else 'catch anything else code, this 'can actually be empty, i.e. do nothing End Select Hope this helps "Derrick" wrote: i have some code on the bottom of one of my sheets, something like this: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub Rows("6:47").EntireRow.Hidden = False If Range("B3") = "Standardized Module Width" Then Rows("6:6").EntireRow.Hidden = False : Rows("15:15").EntireRow.Hidden = False end if end if end sub : Im wondering if its possible to delete the information in cells, eg: F6, F15, ... when another option is selected - so not Standardized module width - i have 3 options... can anyone help? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically deleting data, using macros
thanks,
Ive decided to use Range("F6") = 0 and then i realized i had some formulas in there that were dependent on another cell.. can i write the formulas into this somehow? ie if(B3= "optionA",(if Width = TotalWidth,2,1),0) where Width and totalWidth are pseudonames for cell blocks with the width/total width in them thanks "Jacob Skaria" wrote: Do you mean... Range("F6").Clearcontents Range("F15").Clearcontents If this post helps click Yes --------------- Jacob Skaria "Derrick" wrote: i have some code on the bottom of one of my sheets, something like this: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub Rows("6:47").EntireRow.Hidden = False If Range("B3") = "Standardized Module Width" Then Rows("6:6").EntireRow.Hidden = False : Rows("15:15").EntireRow.Hidden = False end if end if end sub : Im wondering if its possible to delete the information in cells, eg: F6, F15, ... when another option is selected - so not Standardized module width - i have 3 options... can anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically deleting macros | New Users to Excel | |||
Deleting macros | Excel Worksheet Functions | |||
Deleting Macros | Excel Discussion (Misc queries) | |||
How can I make cell data automatically shift up when deleting | Excel Discussion (Misc queries) | |||
How can I make cell data automatically shift up when deleting... | Excel Worksheet Functions |