Deleting rows from a range (Excel 97/SR2)

Topic

New Reply

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.

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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.

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

Reply | Quote

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.

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

Andrew C

Reply | Quote

Thanks to both Andrew and Legare,

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

Reply | Quote