Solved

Skip the current row in Excel if it contains specific text

Posted on 2016-11-07
12
40 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 50

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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

Author Comment

by:Terrygordon
ID: 41877386
Hi Rgonzo

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

Regards

Terry
0
 
LVL 50

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 50

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associateā€¦
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculā€¦
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

839 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