+ Reply to Thread
Results 1 to 4 of 4

Deleting a row based on a value in a column

  1. #1
    Kai
    Guest

    Deleting a row based on a value in a column

    Hi there!

    Over the past few days I've been working on a workflow system in Excel.

    One of the features that was wanted is a clear down function, which will
    automatically delete all off the rows with "Y" in Sheets("Work") columns H
    and I.

    The only thing is I can't seem to get any form of my code to work...

    The majority of snippets that require listing values in certain rows I just
    just the for each cell in rows, if cell.value2 = data then object.additem...
    etc.

    Could someone help me with this code and let me know how I can make it
    workable?

    Private Sub run_cleardown_Click()
    Dim data As String
    data = "Y"
    For Each cell In Sheets("Work").Range("H:H")
    If cell.Value2 = data Then
    EntireRow.Delete cell.Value2
    Else
    End If
    Next
    For Each cell In Sheets("Work").Range("I:I")
    If cell.Value2 = data Then
    EntireRow.Delete cell.Value2
    Else
    End If

    End Sub

    Thanks!

    Kai



  2. #2
    Bob Phillips
    Guest

    Re: Deleting a row based on a value in a column

    Try this

    Private Sub run_cleardown_Click()
    Dim data As String
    Dim iLastrow As Long
    Dim iLastrow1 As Long
    Dim iLastrow2 As Long
    Dim i As Long

    data = "Y"
    With Sheets("Work")
    iLastrow1 = .Cells(Rows.Count, "H").End(xlUp).Row
    iLastrow2 = .Cells(Rows.Count, "I").End(xlUp).Row
    iLastrow = iLastrow1
    If iLastrow2 > iLastrow Then iLastrow = iLastrow2
    For i = iLastrow To 1 Step -1
    If .Cells(i, "H").Value = data Or _
    .Cells(i, "I").Value = data Then
    .Rows(i).Delete
    End If
    Next i
    End With

    End Sub


    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Kai" <hobbaKai@blueyonder.co.uk> wrote in message
    news:fMtbf.7892$j5.5929@fe1.news.blueyonder.co.uk...
    > Hi there!
    >
    > Over the past few days I've been working on a workflow system in Excel.
    >
    > One of the features that was wanted is a clear down function, which will
    > automatically delete all off the rows with "Y" in Sheets("Work") columns H
    > and I.
    >
    > The only thing is I can't seem to get any form of my code to work...
    >
    > The majority of snippets that require listing values in certain rows I

    just
    > just the for each cell in rows, if cell.value2 = data then

    object.additem...
    > etc.
    >
    > Could someone help me with this code and let me know how I can make it
    > workable?
    >
    > Private Sub run_cleardown_Click()
    > Dim data As String
    > data = "Y"
    > For Each cell In Sheets("Work").Range("H:H")
    > If cell.Value2 = data Then
    > EntireRow.Delete cell.Value2
    > Else
    > End If
    > Next
    > For Each cell In Sheets("Work").Range("I:I")
    > If cell.Value2 = data Then
    > EntireRow.Delete cell.Value2
    > Else
    > End If
    >
    > End Sub
    >
    > Thanks!
    >
    > Kai
    >
    >




  3. #3
    Simon Chang
    Guest

    Re: Deleting a row based on a value in a column

    You need this corrections:

    Private Sub run_cleardown_Click()
    Dim data As String
    data = "Y"
    For Each Cell In Sheets("Work").Range("H:H", "I:I")
    If Cell.Value2 = data Then
    Cell.EntireRow.Delete
    Else
    End If
    Next
    End Sub

    Note: make sure you type the value in column H and I as capital letter.

    Good luck.

    "Kai" <hobbaKai@blueyonder.co.uk> wrote in message
    news:fMtbf.7892$j5.5929@fe1.news.blueyonder.co.uk...
    > Hi there!
    >
    > Over the past few days I've been working on a workflow system in Excel.
    >
    > One of the features that was wanted is a clear down function, which will
    > automatically delete all off the rows with "Y" in Sheets("Work") columns H
    > and I.
    >
    > The only thing is I can't seem to get any form of my code to work...
    >
    > The majority of snippets that require listing values in certain rows I

    just
    > just the for each cell in rows, if cell.value2 = data then

    object.additem...
    > etc.
    >
    > Could someone help me with this code and let me know how I can make it
    > workable?
    >
    > Private Sub run_cleardown_Click()
    > Dim data As String
    > data = "Y"
    > For Each cell In Sheets("Work").Range("H:H")
    > If cell.Value2 = data Then
    > EntireRow.Delete cell.Value2
    > Else
    > End If
    > Next
    > For Each cell In Sheets("Work").Range("I:I")
    > If cell.Value2 = data Then
    > EntireRow.Delete cell.Value2
    > Else
    > End If
    >
    > End Sub
    >
    > Thanks!
    >
    > Kai
    >
    >




  4. #4
    Roy Harrill
    Guest

    Re: Deleting a row based on a value in a column

    Kai,

    The main problem is that you seem to be using EntireRow as an object rather
    than as a property. To fix, insert cell. (the object) at the beginning, and
    get rid of cell.Value2 at the end, in those two lines of code. Thus, they
    should be changed to:
    cell.EntireRow.Delete

    cell is the object
    EntireRow is a property of that object
    Delete is a method that operates on the object

    Also, you should dimension cell (Dim cell as Range) at the top, and you need
    to add a Next at the end of your second loop. You can also kill the Else's
    unless you plan to add code for alternative action if data <> "Y"

    The code to achieve your objective could be done more efficiently, but these
    fixes should make it work.

    HTH,
    Roy

    "Kai" <hobbaKai@blueyonder.co.uk> wrote in message
    news:fMtbf.7892$j5.5929@fe1.news.blueyonder.co.uk...
    > Hi there!
    >
    > Over the past few days I've been working on a workflow system in Excel.
    >
    > One of the features that was wanted is a clear down function, which will
    > automatically delete all off the rows with "Y" in Sheets("Work") columns H
    > and I.
    >
    > The only thing is I can't seem to get any form of my code to work...
    >
    > The majority of snippets that require listing values in certain rows I
    > just just the for each cell in rows, if cell.value2 = data then
    > object.additem... etc.
    >
    > Could someone help me with this code and let me know how I can make it
    > workable?
    >
    > Private Sub run_cleardown_Click()
    > Dim data As String
    > data = "Y"
    > For Each cell In Sheets("Work").Range("H:H")
    > If cell.Value2 = data Then
    > EntireRow.Delete cell.Value2
    > Else
    > End If
    > Next
    > For Each cell In Sheets("Work").Range("I:I")
    > If cell.Value2 = data Then
    > EntireRow.Delete cell.Value2
    > Else
    > End If
    >
    > End Sub
    >
    > Thanks!
    >
    > Kai
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1