Announcement

Collapse
No announcement yet.

clear contents of rows based on cell contents in column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • clear contents of rows based on cell contents in column

    Hi All
    I have formulas in cells in column "C" and when a formula returns a "0" (zero)
    I need to empty(not delete) that entire row or rows what ever the case may be, from columns "C,D,E,F,G,H" ect. on wards as far as it will go.I need to have the rows in columns "A,B" untouched , there are 8000 rows.
    Is there a macro that can do this? I'm only a beginner with excel 2000
    Any help will be apperciated
    regards Bill
    Last edited by billgras; October 22nd, 2005, 21:38.

  • #2
    Re: clear contents of rows based on cell contents in column

    If you have the formula in C and clear the contents of C onwards your fomula will be deleted. Is this what you want?
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      Re: clear contents of rows based on cell contents in column

      Try this
      Code:
      '---------------------------------------------------------------------------------------
      ' Module    : Module1
      ' DateTime  : 22/10/2005 14:03
      ' Author    : royUK
      ' Website   : www.excel-it.com
      ' Purpose   : clear cell contents to left of Column c if value = 0
      '---------------------------------------------------------------------------------------
      Option Explicit
        
      Sub ClearContents()
          Dim cl As Range, rCol As Range, RClear As Range
          Set rCol = Range("C2", Range("c65536").End(xlUp))
      Application.ScreenUpdating = False
          For Each cl In rCol
              If cl.Value = 0 Then
                  Set RClear = cl.End(xlToRight)
                   cl.ClearContents 'you can delete this to keep the formula
                  RClear.ClearContents
              End If
          Next cl
      Application.ScreenUpdating = True
      
      End Sub
      Last edited by royUK; October 22nd, 2005, 22:07.
      Hope that Helps

      Roy

      New users should read the Forum Rules before posting

      For free Excel tools & articles visit my web site

      If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

      RoyUK's Web Site

      royUK's Database Form

      Where to paste code from the Forum

      About me.

      Comment


      • #4
        Re: clear contents of rows based on cell contents in column

        thank you for your reply
        it works great

        Comment

        Working...
        X