Results 1 to 4 of 4

copy rows containing given values

Threaded View

  1. #1
    Registered User
    Join Date
    07-28-2010
    Location
    london, england
    MS-Off Ver
    Excel 2007
    Posts
    91

    copy rows containing given values

    I have a workbook with data in columns A:HN.

    What I'm trying to do is copy any rows where the range O:HN contains a 3, 4, 5, 6 or 7 to a new sheet.

    I have been using the following code:

    Sub customcopy()
    Dim strsearch As String, lastline As Integer, tocopy As Integer
    
    strsearch = CStr(InputBox("enter the string to search for"))
    lastline = Range("A65536").End(xlUp).Row
    j = 1
    
    For i = 1 To lastline
        For Each c In Range("o" & i & ":HN" & i)
            If InStr(c.Text, strsearch) Then
                tocopy = 1
            End If
        Next c
        If tocopy = 1 Then
            Rows(i).Copy Destination:=Sheets(4).Rows(j)
            j = j + 1
        End If
    tocopy = 0
    Next i
    
    End Sub
    and running it 5 times (with 3 as string, then 4, then 5 etc...)

    However, I'm getting duplicates. I imagine where rows contain for example a 5 and 6? Is there a more accurate way to do this?
    Last edited by j.farr3ll; 07-07-2014 at 10:47 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA Split Cell Contents to New Rows & Copy Cells containing single values to the new rows
    By jaimelwilson in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-14-2017, 05:30 PM
  2. Replies: 1
    Last Post: 05-26-2014, 09:22 AM
  3. Copy cell values (not formulas) from a pair of rows then paste in a new set of rows
    By pelle3988 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-23-2013, 02:49 PM
  4. Loop to copy all rows containing specific values
    By WDWILSON in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-13-2012, 11:11 PM
  5. [SOLVED] How to copy values in various rows automatically
    By Me in forum Excel General
    Replies: 6
    Last Post: 03-07-2005, 02:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1