Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Location
    Devon, England
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete and Move (Excel 2000)

    I would like to be able to have a macro that when a cell within a certain column ie Column j reached Zero then that row from the sheet that it featured on would delete and the rest of the rows would move up......Is this possible?

    I know the loungers will find this an easy task but I am stumped!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete and Move (Excel 2000)

    I have several questions:
    1. <LI>Do you want this to happen for one particular cell in column J, for instance J10, or for *all* cells in column J?
      <LI>Does the cell (or do the cells) contain a formula, or a value typed by the user, or are both possible?
      <LI>Do you want this to happen automatically? It might be disconcerting to the user if he/she makes a mistake that causes a value to become 0, resulting in deletion of an entire row... Or is it OK to click a button or type a keyboard shortcut to delete the appropriate row(s)?
    Regards,
    Hans

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete and Move (Excel 2000)

    Does the attached worksheet do what you want? Whenever the total for a row becomes zero, it is automatically deleted with the calculate event. I was a little unclear on what you wanted, but I hope this helps. --Sam

    Here is the code: <pre>Option Explicit

    Private Sub Worksheet_Calculate()
    Dim c As Range
    For Each c In Intersect(UsedRange, Columns(5)).Cells
    If IsNumeric(c.Value) Then
    If c.Value = 0 Then c.EntireRow.Delete
    End If
    Next c
    End Sub</pre>

    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    New Lounger
    Join Date
    Aug 2002
    Location
    Devon, England
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete and Move (Excel 2000)

    The project is a stock control list the figures are entered upto supplied column when the product is used the figure is entered there ie 20 supplied 5 used the qty on stock is 15 what I want is that when qty supplied is 20 and qty used 20 the amount on stock is zero so there now no stock so this line to delete. It is fine for the rows to delete automatically when the page opens The column that this needs to b applied to is Column I (indigo) and yes it does have a formula in it.

    I have attached the sheet if that is any clearer than my crap explanations!

    Thanx for your time

    eX
    Attached Files Attached Files

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete and Move (Excel 2000)

    Looks just about the same as mine. Here's the modified code and your workbook with the code. HTH --Sam
    <pre>Option Explicit

    Private Sub Worksheet_Calculate()
    Dim c As Range
    For Each c In Intersect(UsedRange, Columns(6)).Cells
    If IsNumeric(c.Value) Then
    If c.Value - c.Next.Value = 0 Then c.EntireRow.Delete
    End If
    Next c
    End Sub</pre>

    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    New Lounger
    Join Date
    Aug 2002
    Location
    Devon, England
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete and Move (Excel 2000)

    thanx Sam that works great but is there a way that just the information within the cells would be deleted and not the entire table as this will loose any formulas that are in the table

    Thanx 4 your help though its appreciated

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete and Move (Excel 2000)

    In both your previous posts in this thread, you asked for rows to be deleted and the information below them to be moved up. The code provided by Sam does exactly that.

    If you want to clear the appropriate rows completely instead of deleting them, use c.EntireRow.Clear instead of c.EntireRow.Delete.
    If you want to clear the contents, but preserve the formatting, use c.EntireRow.ClearContents.
    In both cases, you'll end up with an empty row.

    If you only want to clear the cell that becomes 0 (thereby removing the formula), use c.Offset(0,4).Clear or c.Offset(0,4).ClearContents. You'll end up with an empty cell in column J.

  8. #8
    New Lounger
    Join Date
    Aug 2002
    Location
    Devon, England
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete and Move (Excel 2000)

    yes my apologies, lack of sleep doesnt help me think straight need more coffee. Thankyou for your help Hans I am new to this and am trudging thru.

Posting Permissions

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