Checking for repeated characters in the excels sheet's row

Hey,

I have a small doubt and I feel really stupid asking this but then could someone help me out in filtering the characters from a cell which are repeating continuously for more than 2 times (Not including 2 times), i.e., for example if there is a "aaa" or any other character (special characters included) anywhere in cell A1 then that particular row needs to be copied to another sheet.

Start

Name Age Address
John 31 skltoijl aaa dsgjsljgl dslfjjjaa
Sharon 26 sdkfjskl sldkjgly eroitbp ei
Bob 45 triyrpoi zlclj asdattt ldfjslll slfkjl
Tim 34 New York, gskldjow
Leo 33 dfjkj ;dsfk;skl;s
Kim 32 akdfjlakflakj
Yeti 19 kksdhslkdhffflkdsjflhj

After

Sheet1

Name Age Address
John 31 skltoijl aaa dsgjsljgl dslfjjjaa
Sharon 26 sdkfjskl sldkjgly eroitbp ei
Bob 45 triyrpoi zlclj asdattt ldfjslll slfkjl
Tim 34 New York, gskldjow
Leo 33 dfjkj ;dsfk;skl;s
Kim 32 akdfjlakflakj
Yeti 19 kksdhslkdhffflkdsjflhj

Sheet2 (The one where the entries satisfying this criterion needs to be copied)

Name Age Address
John 31 skltoijl aaa dsgjsljgl dslfjjjaa
Bob 45 triyrpoi zlclj asdattt ldfjslll slfkjl
Yeti 19 kksdhslkdhffflkdsjflhj

Hope the query is non confusing.

Regards,
Abhushan.

This user defined functionmight help.

Function HasRepeatingLetters(MyString As String) As Boolean
HasRepeatingLetters = False
For N = 1 To 255
    If InStr(MyString, Chr$(N) & Chr$(N) & Chr$(N)) > 0 Then
        HasRepeatingLetters = True
        Exit For
    End If
Next N
End Function

Alt F11 to get to the VBA editor. Add a module Insert- module. Paste in the above. Alt F11 back to sheet. Type

=HasRepeatingLetters(A2)

into an appropriate cell. Then filter on True/False and copy

Originally Posted by mrice

This user defined functionmight help.

Function HasRepeatingLetters(MyString As String) As Boolean
HasRepeatingLetters = False
For N = 1 To 255
    If InStr(MyString, Chr$(N) & Chr$(N) & Chr$(N)) > 0 Then
        HasRepeatingLetters = True
        Exit For
    End If
Next N
End Function

Alt F11 to get to the VBA editor. Add a module Insert- module. Paste in the above. Alt F11 back to sheet. Type

=HasRepeatingLetters(A2)

into an appropriate cell. Then filter on True/False and copy

Thanks for the help, appreciate it.

Please correct me if iam wrong, wont this code just check the whole sheet for repetitive characters? say i want 3 columns out of 10 columns where I need to check for every single row from the top to the bottom regarding repetitive characters, how would that be possible?

Regards,
Abhushan.

Just use it like any formula - add it to one row and copy down.

Originally Posted by mrice

Just use it like any formula - add it to one row and copy down.

Do you mean that add it inside the loop. Iam not getting a clear idea!

Please see the attachment.