It may be convenient for you with a button? Also faster without the sort (in case you delete many rows at once):
Private Sub DeleteText_Click() ' delete a row
Dim xLoop As Long ' xLoop counts rows to be deleted
Dim xMyCell As Range, xMyRange As Range, fRange As Range 'fRange is the range of rows to be deleted
Dim xMyArray() As String ' xMyArray accumulates the unique row numbers to be deleted
Dim xz As Variant ' xz is used for the array search to make sure rows aren't duplicated
Application.ScreenUpdating = False 'set selection to visible cells, ActiveCell if only 1 cell selected; initialize fRange with 1st selected cell
Set xMyRange = IIf(Selection.Count > 1, Selection.SpecialCells(xlCellTypeVisible), ActiveCell): Set fRange = xMyRange(1)
For Each xMyCell In xMyRange ' find rows to be deleted
ReDim Preserve xMyArray(xLoop) ' get unique rows(as string) for duplicate row check
xz = Filter(xMyArray, CStr(xMyCell.Row)) ' check if cell row is already selected, if not add row to range to be deleted
If UBound(xz) < 0 Then xMyArray(xLoop) = CStr(xMyCell.Row): Set fRange = Union(fRange, xMyCell): xLoop = xLoop + 1 'increment counter
Next
If MsgBox("The " & IIf(xLoop = 1, "selected row", xLoop & " selected rows") & " will be deleted", 305, "Confirm Delete . . .") = vbOK Then
For Each xMyCell In fRange.Areas ' in case this is in a table
xMyCell.EntireRow.Delete ' delete range of all rows to be deleted
Next
' or substitute " fRange.EntireRow.Delete " for above 3 lines if not in a table
End If
ActiveCell.Select
Erase xMyArray
End Sub
This code can be attached to a button, then just select any cell in the row (or rows) you wish to delete. If needed, this code can be modified to check if a row is empty using Application.CountA(xMyCell.EntireRow) = 0 before deleting.
Bookmarks