Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Skip the current row in Excel if it contains specific text

Posted on 2016-11-07
12
Medium Priority
?
52 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 53

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Terrygordon
ID: 41877386
Hi Rgonzo

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

Regards

Terry
0
 
LVL 53

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 53

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

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!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

926 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