I want to delete Excel rows dynamically

Question

I have an Excel table that is 6 columns wide (A-F) by 13,000 rows when it opens. I want to write a macro that will begin with cell B1 and check to see if it contains a specified letter (which I will put in another, unused cell). If it does, I want to delete the entire row and move the other rows up. Then the macro should begin again with the B1 and repeat the process. If B1 does not contain the specified letter, I want to successively check C1-F1. If any of them contain the specified letter, I want to delete that row, move the other rows up, and begin again with B1.

If none of the cells B1-F1 contain the specified letter, then I want to leave the row in the table. Then I want to begin testing the next row with B2 (or Bn) I want to continue this process until I have checked Fn in the last row with data, and have either kept or deleted that row.

What I want to be left with is a table containing all the rows from the original set where the specified letter appears in any of the cells in columns 2-6 of the row.

I have been away from Excel macros for twenty years, and so really need some pointers as to how to implement the row deletion, moving up the rows, and hard parts like that.

I find the best way to do this is to start from the bottom of the table, that way a deleted row doesn't impact your loop. OR, build up a list of the rows and then delete them altogether. — Skin, Jan 13 '22 at 06:06
When faced with such a problem, I activate the macro recording, do some of the steps, (e.g. select the line, delete the line) then stop the recording and look at the code generated and copy/paste it into my own code. You can check the value in a cell with the `Value` property of the `Range` object representing the cell, then simply make a loop. And work from the bottom up, it will make things a lot easier! You can use the `UsedRange` property of the active sheet to find which cells are being used, and the `Rows` property of that range to get a list of rows through which you can iterate. — Christopher Hamkins, Jan 13 '22 at 06:48
The question https://stackoverflow.com/questions/70584523/excel-online-excelscript-error-range-getcolumn-timeout-how-do-i-increase-th/70594606#70594606 was about deleting columns with a particular letter, you could adapt the answer to delete rows. — Christopher Hamkins, Jan 13 '22 at 08:16

Christopher Hamkins · Answer 1 · 2022-01-13T17:41:00.510

The fastest way to do this is build up a multiple-area range (that is a read-only operation that won't modify the worksheet) and then delete it in a single operation.

This VBA routine should do it:

Public Sub DeleteRowsHavingCriterion()

    Dim J As Integer
    Dim nrows As Integer
    Dim ws As Worksheet
    Dim UsedRange As Range
    Dim toDeleteRange As Range
    Dim ThisRow As Range
    Dim DeleteThisRow As Boolean
    
    Set ws = Application.ActiveWorkbook.Worksheets("WorksheetToProcess")
    
    Set UsedRange = ws.UsedRange
    Let nrows = UsedRange.Rows.Count
    
    For J = nrows To 1 Step -1
    
        Set ThisRow = UsedRange.Rows(J).EntireRow
        
        DeleteThisRow = ( _
            (ThisRow.Cells(1, 2).Value = "LetterForColumnB") Or _
            (ThisRow.Cells(1, 3).Value = "LetterForColumnC") Or _
            (ThisRow.Cells(1, 4).Value = "LetterForColumnD") Or _
            (ThisRow.Cells(1, 5).Value = "LetterForColumnE") Or _
            (ThisRow.Cells(1, 6).Value = "LetterForColumnF") _
            )
    
        If (DeleteThisRow) Then
            If (toDeleteRange Is Nothing) Then
                Set toDeleteRange = ThisRow
            Else
                Set toDeleteRange = Union(toDeleteRange, ThisRow)
            End If
        End If
    Next J
    
    If (Not (toDeleteRange Is Nothing)) Then
        toDeleteRange.Delete (XlDeleteShiftDirection.xlShiftUp)
    End If

End Sub

I want to delete Excel rows dynamically

1 Answers1