Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Macro to Delete Rows (Excel 2002)

    Hi,
    I'm trying to get this macro to delete the whole row if the word "delete" is located in column I. Obviously I have a coding problem... anyone have any ideas why it isn't working. I don't actually get any error messages... it acts like it's running thru all the rows trying to find the word "delete", but it never actually deletes any rows.
    Thanks!
    Lana

    Sub DeleteRows()

    Dim lngRow As Long

    lngRow = 2

    Do While Sheets("data-complete").Range("A" & lngRow) <> ""

    If Sheets("data-complete").Range("I" & lngRow) = "Delete" Then
    Sheets("data-complete").Range("A" & lngRow).Delete Shift:=xlShiftUp

    End If

    lngRow = lngRow + 1
    Loop

    End Sub

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

    Re: Macro to Delete Rows (Excel 2002)

    When deleting rows, you should loop backwards. And your code doesn't delete the entire row, just the cell in column A. Try this code:

    Sub DeleteRows
    Dim lngRow As Long
    For lngRow = Sheets("data-complete").Range("A65536").End(xlUp).Row To 2 Step -1
    If Sheets("data-complete").Range("I" & lngRow) = "Delete" Then
    Sheets("data-complete").Range("A" & lngRow).EntireRow.Delete
    End If
    Next lngRow
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to Delete Rows (Excel 2002)

    This works perfect Hans... THANK YOU! One more question please... I understand most of the code except the following:

    For lngRow = Sheets("data-complete").Range("A65536").End(xlUp).Row To 2 Step -1

    What does the FOR mean/do? And what does the "To 2 Step -1" mean? Could you explain it in words for me?

    As always, I appreciate your lessons!
    Thanks again!
    Lana

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

    Re: Macro to Delete Rows (Excel 2002)

    The entire part from For lngRow = ... until Next lngRow belongs together.

    Let's start with a simpler example:

    Dim i As Integer
    For i = 1 To 4
    MsgBox i
    Next i

    This means: the variable i will take on the values 1, 2, 3 and 4 successively. For each of these, the code between the lines For i = 1 To 4 and Next i will be executed; in this simple example a message box is displayed that shows the value of i. So the first time, you'll see a message box showing 1, the next time, it'll show 2 etc.

    Unless you specify otherwise, the variable will increase in steps of 1, as in the above example. If I had used

    For i = 1 To 9 Step 2

    the value of i would have increased in steps of 2, so the message box would have shown 1, then 3, 5, 7 and finally 9.

    If you specify a negative value for the Step, the value if i will decrease. For example, if I had used

    For i = 10 To 7 Step -1

    You would have seen 10, then 9, 8 and finally 7.

    Now back to your macro. Sheets("data-complete").Range("A65536") is the bottommost cell in column A.
    End(xlUp) moves up from this cell until it encounters a non-empty cell. It is the equivalent of pressing End, then up-arrow.
    So Sheets("data-complete").Range("A65536").End(xlUp) is the last non-empty cell in column A, and Sheets("data-complete").Range("A65536").End(xlUp).Row is its row number. Let's say the last non-empty cell is in row 37. The line is then equivalent to

    For lngRow = 37 To 2 Step -1

    The variable lngRow will take on the values 37, 36, 35 etc. to 2. In other words, you are looping backwards through the rows.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to Delete Rows (Excel 2002)

    Hans,
    Thanks so much for the detailed explanation... this really helps me to understand the code better. A good portion of the time I just "adapt" a lot of the code you have previously written for me into a new scenario, and with some parts of it I'm not quite sure what it actually means. You are a great teacher! I appreciate your patience in teaching me the ropes! This is fun stuff!
    Lana

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to Delete Rows (Excel 2002)

    Hello!
    I'm hoping it's possible to tweak this code a bit... I've included a sort in my macro to sort the database first, so all the rows that have the word "delete" in column I will be at the top of the database, as opposed to scattered randomly throughout the database. Keeping in mind that there is no set number of rows that have the word "delete" in them. It was my hope that instead of the macro searching and deleting one row at a time (each time it finds the word "delete"), that it could some how "select" ALL the rows that have the word "delete" in them at once, and then do a mass delete all at once? Hope this makes sense??
    Thanks!
    Lana

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

    Re: Macro to Delete Rows (Excel 2002)

    You say that you have included a sort in your macro. Could you show us what your code looks like now so we don't have to guess what it is doing? What else in in the column with the "delete" code? After the sort, do the delete rows always start in a specific row, or will the code have to find the first and the last row?
    Legare Coleman

  8. #8
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to Delete Rows (Excel 2002)

    Hi Legare,
    After my example I've included the code... the database I'm using this on looks like the following:
    Month Unit ID Nature ID Nature Entity Narrative Amount Invoice # Action
    JUL07 84BS 2140060 Accrued Freight Out JOE SHOE testing (560.71) 123 Delete
    JUL07 84BS 2140065 Accrued Freight Income testing 560.71 123 Delete
    JUN07 83BS 2140060 Accrued Freight Out JACK JANE testing 2,300.00 555 Fix
    JUN07 83BS 2140065 Accrued Freight Income testing (2,500.00) 555 Fix

    Ignoring the field heading row, the data starts in A2:I5... I'd like to highlight (select) rows A2:I3, as these two rows have the word delete in column I. Then I'd like to actually delete those two rows from the database. Originally, I didn't have the sort, so all the "delete" rows were located sporadically throughout the database, thus causing the macro to review each and every row and delete one row at a time... the database can be quite large, so it tends to take longer. I was just wondering if there was a faster way of doing it via a macro, and I thought by sorting it first, maybe it would delete faster??
    Thanks Legare!
    Lana

    Sub DeleteRows()

    Dim lngRow As Long

    Rows("3:3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Sort Key1:=Range("I3"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

    For lngRow = Sheets("Data").Range("A10000").End(xlUp).Row To 2 Step -1
    If Sheets("Data").Range("I" & lngRow) = "Delete" Then
    Sheets("Data").Range("A" & lngRow).EntireRow.Delete
    End If
    Next lngRow

    End Sub

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

    Re: Macro to Delete Rows (Excel 2002)

    Here is a fast version of the macro. It doesn't require the data to be sorted; instead, it uses AutoFilter to select the rows containing "Delete" in column I and deletes them.

    Sub DeleteDelete()
    Dim n As Long
    Application.ScreenUpdating = False
    n = Range("I65536").End(xlUp).Row
    Range("A1").AutoFilter Field:=9, Criteria1:="Delete"
    Range("I2:I" & n).SpecialCells(xlCellTypeVisible).EntireRow.Delet e
    ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
    End Sub

  10. #10
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to Delete Rows (Excel 2002)

    Wow, I made that one harder than it needed to be... sorry about that! I certainly wasn't thinking how I would have done it manually in Excel... silly me! Anyway, I would have gotten stuck on the Range("I2:I" & n) part of the code, so I still would have had to ask. Thanks Hans! And thanks to Legare for responding as well... I appreciate the help from you both!
    Lana

Posting Permissions

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