Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2016
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to delete certain cells if certain value is found

    Hi All,
    I would like to build a macro that if the word Repeat is found in column AH then it deletes the data from the same row in columns C through H and R through X (The word repeat will not always be in the same cell and there could be multiple rows that need this action, but the word repeat will always be in the same column). See image for example of before and after. Does anyone know how I would do this?

    Example.jpg

  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
    SPeters,

    Welcome to the Lounge as a New Poster!

    This code should do the trick:
    Code:
    Option Explicit
    
    Sub MyClearData()
    
    Dim lCntr    As Long
    Dim lLastRow As Long
    
    lLastRow = 9 'Replace x with last row number to be processed.
    
    For lCntr = 1 To lLastRow
      
       If (UCase(Cells(lCntr, 34).Value) = "REPEAT") Then
         Range(Cells(lCntr, [3]), Cells(lCntr, [8])).ClearContents
         Range(Cells(lCntr, [18]), Cells(lCntr, [24])).ClearContents
       End If
    
    Next lCntr
    
    End Sub   'MyClearData
    Note: I could have written code to determine the last row but I couldn't see enough of the worksheet to determine the logic.

    Before:
    RPeters1.PNG

    After:
    RPeters2.PNG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Jan 2016
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for the warm welcome! I am new to macros and am having trouble getting that code to work. Are the following steps how I should use your code from above... Click visual basic > insert new module > paste code > run macro? That is what I tried and nothing happened.

  4. #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
    SPeters,

    Yes those are the steps. However, did you change the value of the lLastRow variable as indicated?

    Do you have your Excel set to allow the running of macros?

    File -> Options -> Trust Center -> Trust Center Settings - Macro Settings ->
    SPeters3.PNG
    This (option in Yellow) will suffice for testing but you shouldn't leave it there (operate with the one I have selected in the picture).
    Once you have it working you can change the settings for Trusted Locations to include the directory where you store this file.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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