Advanced record delete.

[Nitefox]

I am currently using the following macro to delete rows:

Sub RowDelete()
With ActiveCell
  If MsgBox("Are you sure you want to delete record number " & Range("A" & .Row) & ", " & Range("F" & .Row) & "?", vbYesNo, "Delete row?") = vbYes Then
    .EntireRow.Delete
  End If
End With
End Sub

How can I take it further so that if the user does not have an active cell selection between (and including) Columns A to K, from row 6 to the last record then is displays the message "You must select a record to be deleted'.
Also if the user selects multiple rows then the message box would instead display the message 'Are you sure you want to delete the records (display the A cell of the first row in the selection) to (display the A cell of the last row in the selection)?
Then erase every row in the selection.

[p24leclerc]

you can try the following macro:

Sub RowDelete()
Set inter_sect = Application.Intersect(Selection, Range("A6", Cells(Rows.Count, 11).End(xlUp)))
If Not inter_sect Is Nothing Then
  With inter_sect 'use inter_sect instead of ActiveCell to allow multiple cells
    If MsgBox("Are you sure you want to delete record number " _
        & Range("A" & .Row) & ", " & Range("A" & .Row + .Count - 1) & "?" _
        , vbYesNo, "Delete row?") = vbYes Then
      .EntireRow.Delete
    End If
  End With
Else
  MsgBox "You must select a record to be deleted", vbInformation, "NO RECORD SELECTED"
End If
End Sub

[Nitefox]

Thank you Pierre, however I would like to make a couple of changes if possible. When there is only one cell selected (and therefore only one row/record bing deleted) it currently displays the record number twice (as its expecting multiple rows). In this situation only can it instead display a different message where the record number is only listed once. So it would read: 'Are you sure you want to delete record number (then display the record number from the A column).'
Also its having difficulty in correctly finding the last record, which I think is because I have formulas in a couple of the columns. I am currently using the following code to successfully find the last row using the G column since the G column does not contain formulas. Can a similar code be used with this code to use G as the column to find the last row?

Cells(Rows.Count, "G").End(xlUp).Offset(1).Select

[p24leclerc]

Here is a modification to show only one record to delete in case only one row is selected.

Sub RowDelete()
Set inter_sect = Application.Intersect(Selection, Range("A6", Cells(Rows.Count, 11).End(xlUp)))
If Not inter_sect Is Nothing Then
  With inter_sect 'use inter_sect instead of ActiveCell to allow multiple cells
    If inter_sect.Count = 1 Then
      If MsgBox("Are you sure you want to delete record number " _
          & Range("A" & .Row), vbYesNo, "Delete row?") = vbYes Then
        .EntireRow.Delete
      End If
    Else
      If MsgBox("Are you sure you want to delete record number " _
          & Range("A" & .Row) & ", " & Range("A" & .Row + .Count - 1) & "?" _
          , vbYesNo, "Delete row?") = vbYes Then
        .EntireRow.Delete
      End If
    End If
  End With
Else
  MsgBox "You must select a record to be deleted", vbInformation, "NO RECORD SELECTED"
End If
End Sub

In the code, I use column K to find the last row of the table because it is also the last column and that we want to be sure any selected cells of the table will trigger the deletion. You can replace the SET line of code with this one if you like. it defines a range from cell A6 to cell K?. The row number is found by looking at the last non-empty cell in a column (actually it is column 1) you select yourself a column which you are sure there will never be something below your table. This is not affected by the cells' content. It can be either a fixed value or a formula.

Set inter_sect = Application.Intersect(Selection, Range("A6:K" & Cells(Rows.Count, 1).End(xlUp).Row))