Solved

Skip the current row in Excel if it contains specific text

Posted on 2016-11-07
12
39 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 49

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:Terrygordon
ID: 41877386
Hi Rgonzo

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

Regards

Terry
0
 
LVL 49

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
 

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 49

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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