Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting rows from a range (Excel 97/SR2)

    How can I use code to automate the deletion of specified row in a range?

    I have a range of data (columns A thru J, and rows 1 to 'varies'), with row 1 being a header row. One of the columns (column G) is department ID, an eight(8) digit number but the cells are formatted as general. There are multiple (10-20) department IDs that I don't require.

    I am looking for a method to cycle through the rows and delete the row if the department ID is one of the ones I do not want to keep. Can someone help me? Please.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting rows from a range (Excel 97/SR2)

    We need a little more information:

    1- Do you want to just look through column G for duplicates and delete them when found? If so, when a two rows have the same value in column G which one do you want to delete?

    2- If the answer to #1 was no, then how do you determine which rows to delete? A predetermined list of department numbers? If so, do you want to delete every row with those department numbers, and if not how do you determine which ones?

    3- Is the data sorted on column G?
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting rows from a range (Excel 97/SR2)

    Legare,

    #1 - No
    #2 - Part A - Yes, I will use a predetermined list of department numbers. I would need to either hardcode the list into the procedure or store them in another file.
    #2 - Part B - Yes, I would like to delete every row whose value in column G is equal to one of the values on the predetermined list.
    #3 - The file is not currently sorted on column G, but I know how to code the sort so that would be fairly easy to do if necessary.

    Thanks.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting rows from a range (Excel 97/SR2)

    The following code assumes that the rows to be deleted are on a worksheet named Sheet1 and that the list of department ID to be deleted is in column A on a sheet named Sheet2 starting in A1.

    <pre>Public Sub DeleteDeptNums()
    Dim lLastI As Long, lLastJ As Long, I As Long, J As Long
    lLastI = Worksheets("Sheet1").Range("G65536").End(xlUp).Row
    lLastJ = Worksheets("Sheet2").Range("A65536").End(xlUp).Row
    For I = lLastI To 2 Step -1
    For J = 1 To lLastJ
    If Worksheets("Sheet1").Range("G1").Offset(I - 1, 0) = _
    Worksheets("Sheet2").Range("A1").Offset(J - 1, 0) Then
    Worksheets("Sheet1").Range("G1").Offset(I - 1, 0).EntireRow.Delete
    Exit For
    End If
    Next J
    Next I
    End Sub
    </pre>

    Legare Coleman

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Deleting rows from a range (Excel 97/SR2)

    I think it is always a good idea to mark records for deletion, and when you are happy that the selected records can be got rid off, then delete. You could insert an additional column in your database in which a lookup formula could match the dept number with a list of numbers for the chop, and enter a specific value if found. It would then be relatively simple to delete the records either by code interactively.

    However, assuming your main body of data is named database, the Dept Id is in Col 7 (G), and you have a list numbers for deletion in a range called "DelDepts", the following code should work by utilising the autofilter facility.<pre>Sub DelRecords()
    Application.ScreenUpdating = False
    Dim oCell As Range
    Dim strDel
    For Each oCell In Range("DelDepts")
    strDel = oCell.Text
    Range("DataBase").AutoFilter Field:=7, Criteria1:=strDel
    Range("A2", Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.EntireRow.Delete
    Next
    Selection.AutoFilter
    Range("A1").Select
    Application.ScreenUpdating = True
    End Sub</pre>



    Andrew C

  6. #6
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting rows from a range (Excel 97/SR2)

    Thanks to both Andrew and Legare,

    Once I recovered from my bout of typing stupidity, I got both pieces of code to work.

Posting Permissions

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