Deletion of highlighted cells macro

**bosetan** · 04-20-2013, 08:40 AM

Hey guys,

I've managed to solve the macro for finding duplicates in name list.
But this time I'd like to have an additional macro for the deletion of those duplicates whose cells are highlighted.

Can anyone help me with this?

Thanks in advance!

Previous macro solved:
http://www.excelforum.com/excel-gene...name-list.html

**HaHoBe** · 04-20-2013, 10:02 AM

Hi, bosetan,

if the cells are highlighted (marked) it would be good enough to use

Selection.Delete

This will only delete the cells.

If you want to delete the rows with the marked cells as well you could use

Selection.EntireRow.Delete

HTH,
Holger

**bosetan** · 04-20-2013, 10:22 AM

Hey HaHoBe,

I've tried creating a new set of macro for just deleting the entire row that has the highlighted cells, using your macro.

But it doesn't seems to work. Any idea what's wrong?

**HaHoBe** · 04-20-2013, 10:26 AM

Hi, bosetan,

But it doesn't seems to work.

Any Error messages? Sheet protected? Workbook protected? Using my dearest enemies: merged cells? To be honest: no idea with that description.

Maybe you could show the code and describe where you placed it?

Ciao,
Holger

**bosetan** · 04-20-2013, 10:30 AM

Hmmm....

Okay. I first start off with creating a new macro, and pasted the macro you gave me into it.
Lastly, assign the new macro to some shape.

**HaHoBe** · 04-20-2013, 10:57 AM

Hi, bosetan,

very good ratio on questions being asked and answers being given.

Public Sub EF916684()
Dim lngLast As Long
Dim lngCounter As Long
Dim rngDelete As Range

Const cstrCOL As String = "D"

lngLast = Cells(Rows.Count, cstrCOL).End(xlUp).Row
For lngCounter = 2 To lngLast
  If WorksheetFunction.CountIf(Range(Cells(2, cstrCOL), Cells(lngCounter, cstrCOL)), Cells(lngCounter, cstrCOL)) > 1 Then
    If rngDelete Is Nothing Then
      Set rngDelete = Cells(lngCounter, cstrCOL)
    Else
      Set rngDelete = Union(rngDelete, Cells(lngCounter, cstrCOL))
    End If
  End If
Next lngCounter

If Not rngDelete Is Nothing Then
  rngDelete.EntireRow.Delete
End If

Set rngDelete = Nothing
End Sub

See attached workbook.

Ciao,
Holger

**bosetan** · 04-20-2013, 11:16 AM

Hey man,

Thanks a lot! It works perfectly now. Is it possible to just delete certain range of cells rather than the entire row?

**HaHoBe** · 04-20-2013, 11:34 AM

Hi, bosetan,

I think so - it depends on if you ever would like to indicate which range?

You could get the information into the range object when building it - this example will delete the range from B to E and get the cells up to fill (not to the left):

Public Sub EF916684_2()
Dim lngLast As Long
Dim lngCounter As Long
Dim rngDelete As Range

Const cstrCOL As String = "D"

lngLast = Cells(Rows.Count, cstrCOL).End(xlUp).Row
For lngCounter = 2 To lngLast
  If WorksheetFunction.CountIf(Range(Cells(2, cstrCOL), Cells(lngCounter, cstrCOL)), Cells(lngCounter, cstrCOL)) > 1 Then
    If rngDelete Is Nothing Then
      Set rngDelete = Range(Cells(lngCounter, 2), Cells(lngCounter, 5))
    Else
      Set rngDelete = Union(rngDelete, Range(Cells(lngCounter, 2), Cells(lngCounter, 5)))
    End If
  End If
Next lngCounter

If Not rngDelete Is Nothing Then
  rngDelete.Delete shift:=xlUp
End If

Set rngDelete = Nothing
End Sub

Ciao,
Holger