How to Find, Highlight A Pattern In A Database And Select Row of the data below pattern.

i have a table A as follows




Id C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12 C13 C14

1 2 3 2 5 1 2 3 2 5 1 2 3 2 5
2 2 4 6 2 2 7 4 6 2 2 7 4 6 2
3 3 3 8 3 3 3 3 8 3 3 3 3 8 3
4 3 5 3 4 9 1 9 8 3 3 3 3 8 3
5 4 4 3 2 4 3 4 3 4 4 3 4 3 4
6 5 5 1 4 5 4 4 3 2 6 5 5 1 1


I need a vba code to select all patterns in my database such as when 2 on top, followed by 2 at bottom column-wise(2 LAPPING 2). Afterwards, I want the code to select the next row below the identified patterns, ie After the identified pattern, what data comes next? : IN THIS INSTANCE ONLY id3 WILL BE SELECTED because only column one (C1) contains the pattern.

Id C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12 C13 C14
3 3 3 8 3 3 3 3 8 3 3 3 3 8 3




I hope I am clear with my problem enough. I am very new to coding!!!

ThanksPattern Datatable.PNG

Assuming Header starts from A1 and the data starts from A3.
If this doesn't work on your side, upload your workbook.

Sub test()
    Dim rng As Range
    Application.ScreenUpdating = False
    With Range("a3").CurrentRegion
        With .Offset(-2).Resize(.Rows.Count + 2)
            Set rng = .Offset(, .Columns.Count + 2).Range("a1:a2")
            rng(2).Formula = "=and(" & .Cells(1, 2).Address(0, 0) & "=2," & _
                .Cells(1, .Columns.Count).Address(0, 0) & "=2)"
            .AdvancedFilter 1, rng
            On Error Resume Next
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Select
            On Error GoTo 0
            .Parent.ShowAllData: rng.Clear
        End With
    End With
    Application.ScreenUpdating = True
End Sub

Attachment 638291

Header is on A1:N1

Column A contains EventID
Column B contains Date
C2:G1000 contains data and
I2:M1000 contains data

Your attachment is not working.

I need to see your WORKBOOK, anyway.

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi jindon

please I've attached a sample data to illustrate my problem further, I hope this brings some clarity in what i want to point out.

Thanks,

Originally Posted by AliGW

Please do not type your thread titles in UPPER CASE - this is considered as shouting online, and we are neither deaf nor daft. It's also hard to read and will not get your question answered any faster.

I have taken note, thanks.