Solved

Skip the current row in Excel if it contains specific text

Posted on 2016-11-07
12
30 Views
Last Modified: 2016-11-08
Hi

I have a macro that loops through from row 2 to the last row in a worksheet and copies the entire row values to another sheet, based on the value in the first column. However, there is another value "Completed", that could appear in any of the other columns, in which case, I don't want it to copy the current row.

How can I check whether the word "Completed" exists in the current row in the loop and skip to a label if it is? The problem is, it won't always be in the same column.

Regards

Terry
0
Comment
Question by:Terrygordon
  • 6
  • 3
  • 3
12 Comments
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 41877058
Hi,

pls try

Sub Macro()
For Each c In Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    If Not Evaluate("=OR(""Completed""=" & c.EntireRow.Address & ")") Then
        'do your copy
    End If
Next
End Sub

Open in new window

Regards
1
 
LVL 45

Expert Comment

by:aikimark
ID: 41877197
clever trick, rgonzo
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41877289
@Terrygordon

Do you have a lot of rows to copy?  You might want to consider block/set copying, rather than line-by-line.
0
 

Author Comment

by:Terrygordon
ID: 41877386
Hi Rgonzo

I am getting a type mismatch error when I run this.

Regards

Terry
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41877390
Could you send a dummy?
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 250 total points
ID: 41877398
@Terry

Is Sheet1 the active worksheet when you run rgonzo's code?  As written, it is a bit sensitive to that condition.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:Terrygordon
ID: 41877407
Hi

I have attached a copy of the sheet it accesses. Unfortunately the rest of the sheets contain confidential information so I can't send these. Basically, the macro needs to look at each row in this sheet and, if the Status column says 'Complete', ignore it - if it says anything else, copy the entire row to another sheet. The macro does a few other things based on fixed columns, but the 'status' column could move if extra columns are added before it.

I am just leaving the office so it may be tomorrow before I can check back on EE.

Regards

Terry
Sample-Temp-Sheet.xlsx
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41877413
in your file you have Complete
Sub Macro()
For Each c In Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    If Not Evaluate("=OR(""Complete""=" & c.EntireRow.Address & ")") Then
        MsgBox " A+"
        'do your copy
    End If
Next
End Sub

Open in new window

we need your code to be able to see the problem
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41877465
It also lacks Sheet2.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41877515
If you only have a hundred or so rows to inspect-and-copy, any method should work.  If you start getting into the thousands of rows, you should look at more efficient techniques.  Here are the performance results from different combinations of inspection and copy based on the posted workbook.
MedianTime	Technique and routine name
1.738281 	Evaluate() & single-row copy (macro)
1.660156 	Evaluate() & area-by-area copy (macro3)
1.300781 	Evaluate() & copy/paste (macro2)
0.9453125	Find & area-by-area copy (macro4)
0.6289063	Find & copy/paste (macro5)

Open in new window

Tested code:
Option Explicit

Sub Macro()
    Dim c As Range
    Dim rngTgt As Range
    Set rngTgt = Worksheets("Sheet2").Range("1:1")
    Application.ScreenUpdating = False
    For Each c In Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
        If Not Evaluate("=OR(""Complete""=" & c.EntireRow.Address & ")") Then
            'do your copy
            'Debug.Print "Copying row: " & c.Row
            rngTgt.Value = c.EntireRow.Value
            Set rngTgt = rngTgt.Offset(1)
        End If
    Next
    Application.ScreenUpdating = True
End Sub
Sub Macro2()
    Dim c As Range
    Dim rngTgt As Range
    Dim rngBuffer As Range
    Set rngTgt = Worksheets("Sheet2").Range("1:1")
    Application.ScreenUpdating = False
    For Each c In Worksheets("Sheet1").Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
        If Not Evaluate("=OR(""Complete""=" & c.EntireRow.Address & ")") Then
            'do your copy
            If rngBuffer Is Nothing Then
                Set rngBuffer = c.EntireRow
            Else
                Set rngBuffer = Union(rngBuffer, c.EntireRow)
            End If
        End If
    Next
    
    If rngBuffer Is Nothing Then
    Else
        rngBuffer.Copy
        rngTgt.PasteSpecial xlPasteValues
    End If
    
    Application.ScreenUpdating = True
End Sub

Sub Macro5()
    Dim c As Range
    Dim rngTgt As Range
    Dim rngBuffer As Range
    Set rngTgt = Worksheets("Sheet2").Range("1:1")
    Application.ScreenUpdating = False
    For Each c In Worksheets("Sheet1").Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
        If c.EntireRow.Find("Complete", c.Cells(1, 1), xlValues) Is Nothing Then
            'do your copy
            If rngBuffer Is Nothing Then
                Set rngBuffer = c.EntireRow
            Else
                Set rngBuffer = Union(rngBuffer, c.EntireRow)
            End If
        End If
    Next
    
    If rngBuffer Is Nothing Then
    Else
        rngBuffer.Copy
        rngTgt.PasteSpecial xlPasteValues
    End If
    
    Application.ScreenUpdating = True
End Sub

Sub Macro3()
    Dim c As Range
    Dim rngTgt As Range
    Dim rngBuffer As Range
    Dim rngA As Range
    
    Set rngTgt = Worksheets("Sheet2").Range("1:1")
    Application.ScreenUpdating = False
    For Each c In Worksheets("Sheet1").Range(Worksheets("Sheet1").Range("A1"), Worksheets("Sheet1").Range("A" & Worksheets("Sheet1").Rows.Count).End(xlUp))
        If Not Evaluate("=OR(""Complete""=" & c.EntireRow.Address & ")") Then
            'do your copy
            'Debug.Print "Copying row: " & c.Row
            If rngBuffer Is Nothing Then
                Set rngBuffer = c.EntireRow
            Else
                Set rngBuffer = Union(rngBuffer, c.EntireRow)
            End If
        End If
    Next
    
    If rngBuffer Is Nothing Then
    Else
        For Each rngA In rngBuffer.Areas
            rngTgt.Worksheet.Range(rngTgt, rngTgt.Offset(rngA.Rows.Count - 1)).Value = rngA.Value
            Set rngTgt = rngTgt.Offset(rngA.Rows.Count)
        Next
    End If
    
    Application.ScreenUpdating = True
End Sub

Sub Macro4()
    Dim wksSrc As Worksheet
    Dim c As Range
    Dim wksTgt As Worksheet
    Dim rngTgt As Range
    Dim rngBuffer As Range
    Dim rngA As Range
    
    Set wksSrc = Worksheets("Sheet1")
    Set wksTgt = Worksheets("Sheet2")
    Set rngTgt = wksTgt.Range("1:1")
    
    Application.ScreenUpdating = False
    For Each c In wksSrc.Range(wksSrc.Range("A1"), wksSrc.Range("A" & wksSrc.Rows.Count).End(xlUp))
        If c.EntireRow.Find("Complete", c.Cells(1, 1), xlValues) Is Nothing Then
            If rngBuffer Is Nothing Then
                Set rngBuffer = c.EntireRow
            Else
                Set rngBuffer = Union(rngBuffer, c.EntireRow)
            End If
        End If
    Next
    
    If rngBuffer Is Nothing Then
    Else
        For Each rngA In rngBuffer.Areas
            wksTgt.Range(rngTgt, rngTgt.Offset(rngA.Rows.Count - 1)).Value = rngA.Value
            Set rngTgt = rngTgt.Offset(rngA.Rows.Count)
        Next
    End If
    
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:Terrygordon
ID: 41879353
Hi Rgonzo

Managed to get a version of your solution to work but Aikimark was right about the sheet reference - the macro looks at several sheets while executing so I just needed to be more specific and run this code on the finished Temp sheet (after all the relevant rows have been copied)rather than on individual rows before they are copied across.

Thank you all for your input.

Regards

Terry
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41879360
That is why I use specific worksheet references in macro4
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now