VBA Search for condition, copy and paste array

**JohnKiernan** · 02-16-2012, 03:17 PM

Hello Everyone!

Forgive me in advance I am new to the forum and do not have much experience with VBA (I am trying to learn).

Currently, I perform a manual task here I have created two additional columns at the end of a set of data. The first column signals the beginning and end of a series (“”, “Begin”, or “End”). The second column checks to see if a condition is met within that series and shows as either “Alert” or “”. I would like to remove the individual rows (from “Begin” to “End”) where the condition is met in column 4 (“Alert”) and move them to a new sheet in the workbook. There can be multiple instances of “Alert” in column 4, and I would like to return only those sequences where there is an “Alert” in that column. That is to say if there is a series of rows (Begin to End) that do not have an Alert in column four I would not wish to see them.

Can someone please point me in the right direction with this?

I have included an attached example

Thank you in advance!

Best,

Jack

Jack_Question.xlsx

**rylo** · 02-16-2012, 05:55 PM

Hi

Try this. It doesn't have any formatting, but I think it does bring across the relevant data.

Sub aaa()
  Dim OutSH As Worksheet
  Set OutSH = Sheets("Sheet2")
  OutSH.Range("A:D").ClearContents
  OutSH.Range("A1:D2").Value = Range("A1:D2").Value
  
  Set findit = Range("D:D").Find(what:="ALERT")
  If Not findit Is Nothing Then
    firstadd = findit.Address
    Do
      begrow = Cells(findit.Row, "C").End(xlUp).Row
      endrow = Cells(findit.Row, "C").End(xlDown).Row
      
      Set findit2 = OutSH.Range("A:A").Find(what:=Range("A" & begrow))
      If findit2 Is Nothing Then 'an instance does not exist
        Range("A" & begrow & ":D" & endrow).Copy
        OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).PasteSpecial (xlPasteValues)
      End If
      Set findit = Range("D:D").Find(what:="ALERT", after:=findit)
    Loop Until findit.Address = firstadd
  End If
  
  Application.CutCopyMode = False
End Sub

rylo