Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Skip the current row in Excel if it contains specific text

Posted on 2016-11-07
12
Medium Priority
?
49 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
12 Comments
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 1000 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 46

Expert Comment

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

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Terrygordon
ID: 41877386
Hi Rgonzo

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

Regards

Terry
0
 
LVL 52

Expert Comment

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

Assisted Solution

by:aikimark
aikimark earned 1000 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
 

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 52

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 46

Expert Comment

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

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 46

Expert Comment

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

721 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