Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 192
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 192
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 192
Default 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
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete row of cells in range based on condition Kirsty Excel Programming 4 January 11th 09 03:37 PM
how to delete a row based on a condition ya Excel Programming 2 December 17th 08 04:59 PM
HOW CAN I DELETE A COLUMN BASED ON A CONDITION ya Excel Programming 1 December 17th 08 01:55 AM
Delete worksheet row based on condition miek Excel Programming 3 August 23rd 07 07:16 PM
Is there a way to delete a cell value based on a condition? Peanut Excel Discussion (Misc queries) 2 October 2nd 06 09:55 PM


All times are GMT +1. The time now is 08:29 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"