Delete rows on a protected sheet based on multiple cells selected.

[paulcherianc]

I am looking for a macro that will delete the Entire row of multiple selected cells in Column B.

I have a sample macro that will delete only the row of selected cell. But I need an extended version. Can anyone help me to fix this?

Sub DeleteRows()

    ActiveSheet.Unprotect Password:="PCode"

    ActiveCell.EntireRow.Select
   
    Selection.Delete
   
    ActiveSheet.Protect "PCode"
          
End Sub

 

[Hui]

Paul

Try this:

Option Base 1

Sub DeleteRows()
'
' Delete all rows in selected cells
' By: Hui
' August 2017
'
ActiveSheet.Unprotect Password:="PCode"
Dim arrDel As Variant
Dim c As Range

Dim myArr() As Long
ReDim myArr(1 To 1) As Long

' Load array with Row Numbers
For Each c In Selection
  myArr(UBound(myArr)) = c.Row
  ReDim Preserve myArr(1 To UBound(myArr) + 1) As Long
Next

'Sort array
Call QuickSort(myArr(), LBound(myArr), UBound(myArr))

'Delete rows in reverse array order
For i = UBound(myArr, 1) To LBound(myArr, 1) + 1 Step -1
  Rows(myArr(i)).Delete
Next

ActiveSheet.Protect "PCode"

End Sub

Public Sub QuickSort(vArray As Variant, inLow As Long, inHi As Long)
'
' Array Sort routine borrowed from:
' https://stackoverflow.com/questions/152319/vba-array-sort-function
'

  Dim pivot  As Variant
  Dim tmpSwap As Variant
  Dim tmpLow  As Long
  Dim tmpHi  As Long

  tmpLow = inLow
  tmpHi = inHi

  pivot = vArray((inLow + inHi) \ 2)

  While (tmpLow <= tmpHi)

  While (vArray(tmpLow) < pivot And tmpLow < inHi)
  tmpLow = tmpLow + 1
  Wend

  While (pivot < vArray(tmpHi) And tmpHi > inLow)
  tmpHi = tmpHi - 1
  Wend

  If (tmpLow <= tmpHi) Then
  tmpSwap = vArray(tmpLow)
  vArray(tmpLow) = vArray(tmpHi)
  vArray(tmpHi) = tmpSwap
  tmpLow = tmpLow + 1
  tmpHi = tmpHi - 1
  End If

  Wend

  If (inLow < tmpHi) Then QuickSort vArray, inLow, tmpHi
  If (tmpLow < inHi) Then QuickSort vArray, tmpLow, inHi

End Sub

The code above
Loads the Row of each cell in the selection into an array
Sorts the array
then
Deletes the rows in reverse order from highest to lowest

 

[paulcherianc]

Many Thanks