• Macro to Delete Rows (Excel 2002)

    Author
    Topic
    #442975

    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

    Viewing 0 reply threads
    Author
    Replies
    • #1067570

      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

      • #1067572

        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

        • #1067576

          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.

          • #1067710

            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

      • #1072634

        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

        • #1072651

          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?

          • #1072662

            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

            • #1072666

              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.Delete
              ActiveSheet.AutoFilterMode = False
              Application.ScreenUpdating = True
              End Sub

            • #1072671

              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

    Viewing 0 reply threads
    Reply To: Macro to Delete Rows (Excel 2002)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: