Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

delete if more then 3

  Asked By: Luana    Date: Dec 11    Category: MS Office    Views: 450
  

I need a vba macro to delete items more than 3.

see this
1
1
1
1
2
2
2
2
3
3
3
3
3
3
I only want to keep 3 cells of each value.
i.e 111222333 in column though I am writing in rows in mail.

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Jimmie Ramirez     Answered On: Dec 11

something like:

Sub deleteem()
intValue = Range("a1").Value
For x = 1 To 100
If Range("a" & x).Value = intValue Then
intCount = intCount + 1
Else
intCount = 0
intValue = Range("a" & x).Value
End If
If intCount > 3 Then
Range("a" & x).Delete
End If
Next x
End Sub

 
Answer #2    Answered By: Gilberto Thompson     Answered On: Dec 11

You need to loop through a range with a counter, test if the current
item is the same as the last one and if it is delete  it.

The following code works for me.

Be aware that if you delete the row then the selected range will stay
the same size. Also.. the code starts with A1.

 
Didn't find what you were looking for? Find more on delete if more then 3 Or get search suggestion and latest updates.




Tagged: