Clear Cells

  • Thread starter Thread starter ksnapp
  • Start date Start date
K

ksnapp

I also need a sub that clears (not deletes) duplicate name in column
(but keeps the first instance
 
Sub AAAA()
Dim rng As Range
Set rng = Range("A1")
Do While Not IsEmpty(rng)
If Application.CountIf(Range(Cells(1, 1), _
Cells(rng.Row, 1)), Cells(rng.Row, 1)) > 1 Then
rng.ClearContents
End If
Set rng = rng.Offset(1, 0)
Loop

End Sub
 
I pasted this right in to the editor and ran it, it doesn't do anything
No errors or the like but nothin on the sheet changes?

think it'll work if i speicfy my range, the data is not contigous?

Thank Yo
 
Can you read code?

As written it stops when it hits an empty cell.

It ran fine for me on contiguous data. (which was my assumption since you
stated no conditions).

This should work with data that contains empty cells:

Sub bbbb()
Dim rng As Range
Set rng = Cells(Rows.Count, 1).End(xlUp)
Do While Not rng.Row = 1
If Not IsEmpty(rng) Then
If Application.CountIf(Range(Cells(1, 1), _
Cells(rng.Row, 1)), Cells(rng.Row, 1)) > 1 Then
rng.ClearContents
End If
End If
Set rng = rng.Offset(-1, 0)
Loop
End Sub
 
I thought I could almost read code. Sorry I should have specified no
contigous data. Thank you so much for your help. You also write in
different style then I am use to so you code should keep me busy fo
hours. Again thank you.

Kevi
 
Back to the original - removing everything but the loop structure

set rng = Range("A1") ' start rng in A1
Do While Not IsEmpty(rng) ' loop while cell (rng) is not empty
Set rng = rng.Offset(1, 0) ' drop down one cell
Loop

should show that it starts in A1 and progresses down column A until it finds
an empty cell.

If A1 is empty, it never enters the loop.

My point was that if you read the code, it would be obvious why it initially
did nothing if you had an empty cell before you reached any duplicates.
 
Back
Top