Results 1 to 4 of 4

Thread: Clear Cell Contents based on Cell Value
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2008
    Location
    United Kingdom
    Posts
    147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Clear Cell Contents based on Cell Value

    Afternoon all,

    I'm hoping someone can help me.

    I need to be able to clear the contents of cells in column C, D, E, H if the value of column Q equals HELLO for arguments sake.

    Clearing the contents of an entire row is not a problem, however when it comes to resizing rows I seem to come unstuck.

    Related thread: http://www.mrexcel.com/forum/showthread.php?t=476993

    Any help would be appreciated.

  2. #2
    Board Regular
    Join Date
    Jul 2009
    Location
    Lancashire, England
    Posts
    1,249
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clear Cell Contents based on Cell Value

    Hello

    I may have missed the point, and grossly oversimplified things.
    So please try the below on a COPY of your data.

    Sub SDave()
    Set sh = Sheets("Successors_for_Roles")
      lr = sh.Rows(sh.UsedRange.Rows.Count).Row
      Application.ScreenUpdating = False
        For i = 1 To lr
          If Cells(i, "Q") = "HELLO" Then
            sh.Cells(i, "C").Resize(, 3).ClearContents
            sh.Cells(i, "H").ClearContents
          End If
        Next i
      Application.ScreenUpdating = True
    End Sub
    HTH
    Brian.

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    Xl 2007 & 2010 Win 7

  3. #3
    Board Regular
    Join Date
    Aug 2008
    Location
    United Kingdom
    Posts
    147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clear Cell Contents based on Cell Value

    Thanks Brian, your a star. Much appreciated. That's exactly what I was after.

  4. #4
    New Member
    Join Date
    Dec 2013
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clear Cell Contents based on Cell Value

    Hi,

    Can you please let me know how i can make it dynamic. I mean it should check each row one and by one with the range of 1-20000 rows automatically.

    its very urgent...Many thanks in advance

    Thanks
    Zenta


    Quote Originally Posted by meldoc View Post
    Hello

    I may have missed the point, and grossly oversimplified things.
    So please try the below on a COPY of your data.

    Sub SDave()
    Set sh = Sheets("Successors_for_Roles")
    **lr = sh.Rows(sh.UsedRange.Rows.Count).Row
    **Application.ScreenUpdating = False
    ****For i = 1 To lr
    ******If Cells(i, "Q") = "HELLO" Then
    ********sh.Cells(i, "C").Resize(, 3).ClearContents
    ********sh.Cells(i, "H").ClearContents
    ******End If
    ****Next i
    **Application.ScreenUpdating = True
    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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