Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: clear contents of rows based on cell contents in column

  1. #1
    Join Date
    1st June 2005
    Posts
    15

    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 at 21:38.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,145

    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

    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.

  3. #3
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,145

    Re: clear contents of rows based on cell contents in column

    Try this
    VB:
     '---------------------------------------------------------------------------------------
     ' Module    : Module1
     ' DateTime  : 22/10/2005 14:03
     ' Author    : royUK
     ' Website   : [url]www.excel-it.com[/url]
     ' 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 at 22:07.
    Hope that Helps

    Roy

    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.

  4. #4
    Join Date
    1st June 2005
    Posts
    15

    Re: clear contents of rows based on cell contents in column

    thank you for your reply
    it works great

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Clear cell contents based on multiple criteria
    By Mactn113 in forum EXCEL HELP
    Replies: 5
    Last Post: December 19th, 2005, 22:09
  2. Clear cell contents based on background color
    By rebel in forum EXCEL HELP
    Replies: 1
    Last Post: August 25th, 2005, 04:54
  3. Clear Contents of Rows with 1 empty cell
    By peteroldfield in forum EXCEL HELP
    Replies: 3
    Last Post: April 27th, 2005, 23:50
  4. Deleting contents of rows based on cell contents
    By adncmm1980 in forum EXCEL HELP
    Replies: 2
    Last Post: November 22nd, 2004, 16:17

Bookmarks

Posting Permissions

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