Delete cells based on condition
 

I've been struggling with this for a couple of hours and would much
appreciate some help before I give up please!

I want to delete a cell in col A if it is the second of two identical
cells in col A AND col B is empty.

https://dl.dropboxusercontent.com/u/...eSomeCells.jpg

--
Terry, East Grinstead, UK

Delete cells based on condition
 

Hi Terry,

Am Thu, 26 May 2016 20:58:45 +0100 schrieb Terry Pinnell:

https://dl.dropboxusercontent.com/u/...eSomeCells.jpg

try:

Sub Test()
Dim LRow As Long, i As Long

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To LRow
If Len(.Cells(i, 2)) = 0 And _
Application.CountIf(Range("A3:A" & i), .Cells(i, 1)) 1
Then
.Cells(i, 1).ClearContents
End If
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Delete cells based on condition
 

Claus Busch wrote:

Hi Terry,

Am Thu, 26 May 2016 20:58:45 +0100 schrieb Terry Pinnell:

https://dl.dropboxusercontent.com/u/...eSomeCells.jpg

try:

Sub Test()
Dim LRow As Long, i As Long

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To LRow
If Len(.Cells(i, 2)) = 0 And _
Application.CountIf(Range("A3:A" & i), .Cells(i, 1)) 1
Then
.Cells(i, 1).ClearContents
End If
Next
End With
End Sub


Regards
Claus B.

Thanks for the fast reply, Claus, much appreciated.

Have to go out now so will study more carefully tonight, but first
couple of attempts gave me this error message:

https://dl.dropboxusercontent.com/u/...ro11-Error.jpg

--
Terry, East Grinstead, UK

Delete cells based on condition
 

Hi Terry,

Am Fri, 27 May 2016 12:10:26 +0100 schrieb Terry Pinnell:

Have to go out now so will study more carefully tonight, but first
couple of attempts gave me this error message:

https://dl.dropboxusercontent.com/u/...ro11-Error.jpg

the command has to be in one line:

Sub Test()
Dim LRow As Long, i As Long

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To LRow
If Len(.Cells(i, 2)) = 0 And Application _
.CountIf(.Range("A3:A" & i), .Cells(i, 1)) 1 Then
.Cells(i, 1).ClearContents
End If
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Delete cells based on condition
 

Hi Terry,

Am Fri, 27 May 2016 12:10:26 +0100 schrieb Terry Pinnell:

https://dl.dropboxusercontent.com/u/...ro11-Error.jpg

try it this way:

Sub Test()
Dim LRow As Long, i As Long

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 1 To LRow
If Len(.Cells(i, 2)) = 0 And Application _
.CountIf(.Range("A:A"), .Cells(i, 1)) 1 Then
.Cells(i, 1).ClearContents
End If
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Delete cells based on condition
 

Claus Busch wrote:

Hi Terry,

Am Fri, 27 May 2016 12:10:26 +0100 schrieb Terry Pinnell:

https://dl.dropboxusercontent.com/u/...ro11-Error.jpg

try it this way:

Sub Test()
Dim LRow As Long, i As Long

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 1 To LRow
If Len(.Cells(i, 2)) = 0 And Application _
.CountIf(.Range("A:A"), .Cells(i, 1)) 1 Then
.Cells(i, 1).ClearContents
End If
Next
End With
End Sub


Regards
Claus B.

Brilliant, you're a star! Both of those worked, and for both of the
scenarios.

I'd got a sort of result by abandoning VBA and writing my own macro.
But apart from being unreliable it was glacially slow by comparison
with your macro.

Thank you so much.

--
Terry, East Grinstead, UK

Delete cells based on condition
 

Hi Terry,

Am Fri, 27 May 2016 19:25:49 +0100 schrieb Terry Pinnell:

Brilliant, you're a star! Both of those worked, and for both of the
scenarios.

you are welcome. Always glad to help.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional