Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Mar 2015
    Posts
    21
    Thanks
    41
    Thanked 0 Times in 0 Posts

    Delete cell contents if a condition is met

    Hi, all. I'm trying to implement a variation of the situation found here (http://windowssecrets.com/forums/sho...in-cells/page2), as related to a previous post of mine (http://windowssecrets.com/forums/sho...Excel?p=995089)

    What I'm wondering is whether I can create a macro that when I manually select a range will remove all values in any cell that are greater than 25, say.


    I've recorded macros that will, for example, replace all "N/A"''s or all numeric characters, but how do I stipulate that the only values that are to be removed are those greater than 25?...


    Steve (sdckapr) had posted this code before here (http://windowssecrets.com/forums/sho...-certain-cells) and Maude and RetiredGeek had worked on a solution in that thread, too:


    Quote Originally Posted by sdckapr View Post
    ... It would take a macro to delete the contents. Something like this in the worksheet object:Steve

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Not Intersect(Target, Range("A1")) Is Nothing Then
        If UCase(Range("A1")) = "YES" Then
          Application.EnableEvents = False
          Range("A2").ClearContents
          Range("A3").ClearContents
          Application.EnableEvents = True
        End If
      End If
    End Sub
    I also found this code on another site, here (http://stackoverflow.com/questions/2...c-cell-value):

    Code:
    Dim myCell As Range
    numberOfRows = 1000
    
    For i = 0 To numberOfRows 
        Dim myRow As Range
    
        Set myRow = [C3:ACP3].Offset(i, 0)
        bound = Intersect([acu3].EntireColumn, myRow.EntireRow) 
    
        For Each myCell In myRow
            If myCell >= bound Then myCell = ""
        Next
    Next
    but I think this may be a little more complicated than what I want...


    Can someone show me a way to modify this code for it to do what I need?... Thanks!
    Last edited by olives; 2015-04-08 at 11:06.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Olives,

    This should do the trick:
    Code:
    Option Explicit
    
    Sub ValueRangeDelete()
    
       Dim cell As Range
       Dim dTriggerValue As Double
       
       dTriggerValue = Val(InputBox("Enter Clear Value:", "Range Clearing Value"))
       
       If dTriggerValue = 0 Then Exit Sub
       
       For Each cell In Selection
         
          If cell.Value >= dTriggerValue Then cell.ClearContents
       
       Next cell
       
    End Sub
    Note: the trigger value was defined as double as I didn't know what type of numbers you would be using. It should work for any type this way however with large decimal numbers you may have some precision problems with the compare.

    I've also added a prompt for the trigger value to make it more versatile but you could easly remove that and hard code the value if desired.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    olives (2015-04-08)

  4. #3
    New Lounger
    Join Date
    Mar 2015
    Posts
    21
    Thanks
    41
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    ... This should do the trick...
    G, got it... I'll try it out later tonight...I do have a question, can I set the criteria I wish with this macro? For example, am I able to say 'delete any value greater than 25' ?

    I'll probably just be deleting integers, really... (referring to you mentioning 'double')...

    I haven't run it, but am I right it will display a dialog box and ask me to input the value to be cleared?... If I'm right, could I run this without a dialog box?... Just have it coded so when I select a range with the mouse then I just run the macro with the criteria, say '>25', coded into the macro code?...

    I just don't change the criteria very much and would prefer not to have to enter a value every time I run it... I may use it for weeks with the same criteria and would be ok with either setting up a different macro with other criteria or just changing the code in the VB editor if I needed to...

    Thanks! I'll run this tonight at home to check it out... You're quick and I'm amazed, RG...
    Last edited by olives; 2015-04-08 at 12:01.

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Olives,

    You are correct:
    Code:
    Option Explicit
    
    Sub ValueRangeDelete()
    
       Dim cell As Range
        
       For Each cell In Selection
         
          If cell.Value > 25 Then cell.ClearContents
       
       Next cell
       
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    olives (2015-04-08)

  7. #5
    New Lounger
    Join Date
    Mar 2015
    Posts
    21
    Thanks
    41
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    ,... You are correct... HTH
    G, you've made me smile big and wide... I'm like the happy father of an newborn baby... You're going to make me look good at work... Thank you!

  8. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi RG

    ..a user must be careful not to include any headers in the selected range, as this routine will delete all text entries in the range.

    zeddy

  9. The Following User Says Thank You to zeddy For This Useful Post:

    olives (2015-04-08)

  10. #7
    New Lounger
    Join Date
    Mar 2015
    Posts
    21
    Thanks
    41
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    ... this routine will delete all text entries in the range...
    Thanks, zeddy... By the way, everyone at work loves your spreadsheet!

  11. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Boy I'm telling you it's a tough crowd around here.

    Code:
    Option Explicit
    
    Sub ValueRangeDelete()
    
       Dim cell As Range
        
       For Each cell In Selection
       
          If WorksheetFunction.IsNumber(cell) Then _
            If cell.Value > 25 Then cell.ClearContents
       
       Next cell
       
    End Sub 'ValueRangeDelete
    Before:
    OlivesBefore.JPG
    After:
    OlivesAfter.JPG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. The Following User Says Thank You to RetiredGeek For This Useful Post:

    olives (2015-04-10)

  13. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    To go just a little further:

    Prevent deletion of formulas that evaluate to > 25:
    Code:
    Option Explicit
    
    Sub ValueRangeDelete()
    
       Dim cell As Range
        
       For Each cell In Selection
       
          If WorksheetFunction.IsNumber(cell) Then _
            If Not cell.HasFormula Then _
              If cell.Value > 25 Then cell.ClearContents
       
       Next cell
       
    End Sub 'ValueRangeDelete
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    One more time!

    Keep it from killing dates:
    Code:
    Option Explicit
    
    Sub ValueRangeDelete()
    
       Dim cell As Range
       Dim bNotDate As Boolean
         
       For Each cell In Selection
       
          bNotDate = True
          On Error Resume Next
            bNotDate = IsError(DateValue(cell))
          On Error GoTo 0
          
          If WorksheetFunction.IsNumber(cell) Then _
            If Not cell.HasFormula Then _
              If bNotDate Then _
                If cell.Value > 25 Then cell.ClearContents
       
       Next cell
       
    End Sub 'ValueRangeDelete
    I'm DONE!

    Of course back in my working days we'd point to the specification and say it ain't in there!ROTFLOL.gif

    HTH
    Last edited by RetiredGeek; 2015-04-08 at 15:30.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. The Following 2 Users Say Thank You to RetiredGeek For This Useful Post:

    olives (2015-04-10),zeddy (2015-04-08)

  16. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi RG

    Brilliant as always!
    That is top notch.
    Even ignores #ERRORS
    You are the man!

    zeddy
    PS ..specs. we no need no stinkin specs.

  17. The Following User Says Thank You to zeddy For This Useful Post:

    olives (2015-04-10)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •