How to move entire row from "Check for repeat alarms" to "lights" based on cell value TRUE in Column G

[VBAPixie]

Hi,

I think i have bite of more than I can chew.. looking for some help please?

Ideally i was trying to write VBA to check sheet "CFRA" in column G for the word "lighting" IF true move entire row that contains lighting to "lights"sheet.

With no luck I used a formula to deter if "lighting" is TRUE.

Now i need to move entire row if column F is TRUE

the formula i am using is

=IFERROR(IF(FIND("lighting",LOWER(G10:G29),1)>=1,TRUE),FALSE)

Could anything be added to this for it to move entire row to the other worksheet or can someone help write VBA to move the entire row based on true value?

Any help/advice would be greatly appreciated!

 

[JackDanIce]

Welcome to the board.

Try:

Sub M1()

    Dim x   As Long
    
    Application.ScreenUpdating = False
    
    With sheets("CFRA")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        With .Cells(1, 7).Resize(x)
            .AutoFilter
            .AutoFilter field:=1, Criteria1:="lightening"
            .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Copy
        End With
    End With
    
    With sheets("lights")
        .Cells(.Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    End With
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
    
End Sub

 

[My Aswer Is This]

Try this:

Sub Filter_Me_Please()
'Modified  9/30/2018  11:39:13 AM  EDT
Application.ScreenUpdating = False
Sheets("CFRA").Activate
Dim lastrow As Long
Dim lastrowa As Long
Dim c As Long
Dim s As Variant
c = "7" ' Column Number Modify this to your need
s = "lighting" 'Search Value Modify to your need
lastrow = Cells(Rows.Count, c).End(xlUp).Row
lastrowa = Sheets("lights").Cells(Rows.Count, c).End(xlUp).Row + 1
With ActiveSheet.Cells(1, c).Resize(lastrow)
    .AutoFilter 1, s
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("lights").Rows(lastrowa)
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

 

[JackDanIce]

Both codes have little difference code in #2 will fail if no filtered values found.

Curious why c is declared as a type long in #3 but “7” as string is then passed to it, surely that’s careless assignment?

Or does Cells take column index value as a string data type over number?