Searching a row A for 2 values - how to extend to Row U and copy + paste those to new sheet?

anon500 · Jul 17, 2020

I have data pull that I would like to arrange and sort by finding 'tables' in the sheet and copying and pasting those tables out to different sheets

Sub SelectBetween()
On Error GoTo errhandler
Dim FinalRow As Integer

Worksheets("Dump").Activate
Range(Range("A:A").Find("test1").Offset(1), Range("A:A").Find("test2", Range("A:A").Find("test1")).Offset(-1)).Select
FinalRow = Cells(Rows.Count, "C").End(xlUp).Row
Selection.Copy
Worksheets("Sheet2").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Exit Sub
errhandler:
MsgBox "No Cells containing specified text found"
End Sub

So above searches Sheet 'Dump' finds Test 1 to Test 2 table, copies only Range A1 and pastes into the cell I'm hovered over, how could this be changed to paste into Range A1?

The FinalRow = Cells(Rows.Count, "C").End(xlUp).Row doesn't do anything yet, I need to figure out how to put this into my code, I was trying to get it to select till final row instead of only Row A

Very bad coding practices probably!

MARK858 · Jul 18, 2020

Sorry but I am a bit confused by your description. You state that the code as is copies only Range A1 but the code selects the highlighted range below when I run it.

Because of the area the code is highlighting below I don't understand how you want FinalRow to be used.

You are also referring to Row a lot when Range("A:A") is a column (If you are using Excel defaults) and you don't state anything about "how to extend to Row U" which you mention in the title (I assume that you mean column U to start with)

Can you try and clarify please using the data below as an example.

Book1

A

B

C

1

SP Name

Order ID

Order Date

2

Andrew Fuller

108-14-N31

14/01/2014

3

Margaret Peacock

102-26-N89

26/08/2012

4

Test1

109-19-F60

19/03/2014

5

Anne Dodsworth

107-16-N07

16/10/2013

6

Robert King

107-19-F86

19/12/2013

7

Laura Callahan

109-17-N56

17/03/2014

8

Laura Callahan

104-20-N81

20/03/2013

9

Laura Callahan

105-26-N48

26/05/2013

10

Nancy Davolio

104-1-F92

01/04/2013

11

Anne Dodsworth

107-23-F15

23/10/2013

12

Margaret Peacock

109-20-N65

20/03/2014

13

Nancy Davolio

109-26-F13

26/02/2014

14

Robert King

110-28-F54

28/04/2014

15

Nancy Davolio

107-10-F00

10/10/2013

16

Test2

106-9-F98

09/10/2013

17

Margaret Peacock

107-19-N46

19/11/2013

18

Laura Callahan

16/01/2014

19

Michael Suyama

20

Janet Leverling

21

Nancy Davolio

Dump

anon500 · Jul 18, 2020

MARK858 said:
Sorry but I am a bit confused by your description. You state that the code as is copies only Range A1 but the code selects the highlighted range below when I run it.

Because of the area the code is highlighting below I don't understand how you want FinalRow to be used.

You are also referring to Row a lot when Range("A:A") is a column (If you are using Excel defaults) and you don't state anything about "how to extend to Row U" which you mention in the title (I assume that you mean column U to start with)

Can you try and clarify please using the data below as an example.

Book1
A B C
1 SP Name Order ID Order Date
2 Andrew Fuller 108-14-N31 14/01/2014
3 Margaret Peacock 102-26-N89 26/08/2012
4 Test1 109-19-F60 19/03/2014
5 Anne Dodsworth 107-16-N07 16/10/2013
6 Robert King 107-19-F86 19/12/2013
7 Laura Callahan 109-17-N56 17/03/2014
8 Laura Callahan 104-20-N81 20/03/2013
9 Laura Callahan 105-26-N48 26/05/2013
10 Nancy Davolio 104-1-F92 01/04/2013
11 Anne Dodsworth 107-23-F15 23/10/2013
12 Margaret Peacock 109-20-N65 20/03/2014
13 Nancy Davolio 109-26-F13 26/02/2014
14 Robert King 110-28-F54 28/04/2014
15 Nancy Davolio 107-10-F00 10/10/2013
16 Test2 106-9-F98 09/10/2013
17 Margaret Peacock 107-19-N46 19/11/2013
18 Laura Callahan 16/01/2014
19 Michael Suyama
20 Janet Leverling
21 Nancy Davolio
Dump

Hello

Thank you for replying

So basically my macro would search SP name for tables, in which case your one would be Test1 to Test2, and then also extend out to include any other columns (order id and order date)

With this selection found it should copy this table to a new sheet (Sheet 2) at the A1.

MARK858 · Jul 18, 2020

Maybe...

VBA Code:

Sub SelectBetween()
    Dim sRow As Range, erow As Range, lstCol As Long

    On Error GoTo errhandler

    Set sRow = Worksheets("Dump").Range("A:A").Find("test1").Offset(1)
    Set erow = Worksheets("Dump").Range("A:A").Find("test2").Offset(-1)
    lstCol = Worksheets("Dump").Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column

    Range(sRow, erow).Resize(, lstCol).Copy Worksheets("Sheet2").Range("A1")

    Exit Sub

errhandler:
    MsgBox "No Cells containing specified text found"

End Sub

anon500 · Jul 21, 2020

Anyone please?

MARK858 said:

Maybe...

VBA Code:

Sub SelectBetween()
    Dim sRow As Range, erow As Range, lstCol As Long

    On Error GoTo errhandler

    Set sRow = Worksheets("Dump").Range("A:A").Find("test1").Offset(1)
    Set erow = Worksheets("Dump").Range("A:A").Find("test2").Offset(-1)
    lstCol = Worksheets("Dump").Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column

    Range(sRow, erow).Resize(, lstCol).Copy Worksheets("Sheet2").Range("A1")

    Exit Sub

errhandler:
    MsgBox "No Cells containing specified text found"

End Sub

Thank you this works amazingly!

is there a way to adapt Range.Find to find the 4th occurence of the text?

MARK858 · Jul 21, 2020

anon500 said:
is there a way to adapt Range.Find to find the 4th occurence of the text?

Yes but could you have not asked this in the first post if that is what you wanted as it is totally different code?
And is this 4th occurrence the start or the finish of the range?

MARK858 · Jul 21, 2020

Assuming that it is the start of the range (Test1) then try...

VBA Code:

Sub FindIt2()
    Dim fCell As Range, lCell As Range, fAddr As String
    Dim i As Long, srow As Range, erow As Range, lstCol As Long
    
    i = 0
    
    With Worksheets("Dump").Range("A2:A" & Worksheets("Dump").Range("A" & Rows.Count).End(xlUp).Row)
        Set lCell = .Cells(.Cells.Count)

        Set fCell = .Find(What:="Test1", After:=lCell, LookIn:=xlValues, LookAt:= _
                          xlPart, SearchOrder:=xlByRows)

        If Not fCell Is Nothing Then
            fAddr = fCell.Address
      
        End If

        Do Until fCell Is Nothing
            
            Set fCell = .FindNext(After:=fCell)
            i = i + 1
            If i = 3 Then
                Set srow = fCell.Offset(1)
                Exit Do
            End If
            If fCell.Address = fAddr Then
                Exit Do
            End If
        Loop
    End With
  
  
 On Error GoTo errhandler

    
    Set erow = Worksheets("Dump").Range("A:A").Find("test2").Offset(-1)
    lstCol = Worksheets("Dump").Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column

    Range(srow, erow).Resize(, lstCol).Copy Worksheets("Sheet2").Range("A1")

    Exit Sub

errhandler:
    MsgBox "No Cells containing specified text found"
    
End Sub

anon500 · Jul 22, 2020

MARK858 said:
Sorry but I am a bit confused by your description. You state that the code as is copies only Range A1 but the code selects the highlighted range below when I run it.

Because of the area the code is highlighting below I don't understand how you want FinalRow to be used.

You are also referring to Row a lot when Range("A:A") is a column (If you are using Excel defaults) and you don't state anything about "how to extend to Row U" which you mention in the title (I assume that you mean column U to start with)

Can you try and clarify please using the data below as an example.

Book1
A B C
1 SP Name Order ID Order Date
2 Andrew Fuller 108-14-N31 14/01/2014
3 Margaret Peacock 102-26-N89 26/08/2012
4 Test1 109-19-F60 19/03/2014
5 Anne Dodsworth 107-16-N07 16/10/2013
6 Robert King 107-19-F86 19/12/2013
7 Laura Callahan 109-17-N56 17/03/2014
8 Laura Callahan 104-20-N81 20/03/2013
9 Laura Callahan 105-26-N48 26/05/2013
10 Nancy Davolio 104-1-F92 01/04/2013
11 Anne Dodsworth 107-23-F15 23/10/2013
12 Margaret Peacock 109-20-N65 20/03/2014
13 Nancy Davolio 109-26-F13 26/02/2014
14 Robert King 110-28-F54 28/04/2014
15 Nancy Davolio 107-10-F00 10/10/2013
16 Test2 106-9-F98 09/10/2013
17 Margaret Peacock 107-19-N46 19/11/2013
18 Laura Callahan 16/01/2014
19 Michael Suyama
20 Janet Leverling
21 Nancy Davolio
Dump

Hello

Thank you for replying

So basically my macro would search SP name for tables, in which case your one would be Test1 to Test2, and then also extend out to include any other columns (order id and order date)

With this selection found it should copy this table to a new sheet (Sheet 2) at the A1.

Then if possible it should sort (Order Date)

MARK858 said:

Assuming that it is the start of the range (Test1) then try...

VBA Code:

Sub FindIt2()
    Dim fCell As Range, lCell As Range, fAddr As String
    Dim i As Long, srow As Range, erow As Range, lstCol As Long
   
    i = 0
   
    With Worksheets("Dump").Range("A2:A" & Worksheets("Dump").Range("A" & Rows.Count).End(xlUp).Row)
        Set lCell = .Cells(.Cells.Count)

        Set fCell = .Find(What:="Test1", After:=lCell, LookIn:=xlValues, LookAt:= _
                          xlPart, SearchOrder:=xlByRows)

        If Not fCell Is Nothing Then
            fAddr = fCell.Address
     
        End If

        Do Until fCell Is Nothing
           
            Set fCell = .FindNext(After:=fCell)
            i = i + 1
            If i = 3 Then
                Set srow = fCell.Offset(1)
                Exit Do
            End If
            If fCell.Address = fAddr Then
                Exit Do
            End If
        Loop
    End With
 
 
On Error GoTo errhandler

   
    Set erow = Worksheets("Dump").Range("A:A").Find("test2").Offset(-1)
    lstCol = Worksheets("Dump").Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column

    Range(srow, erow).Resize(, lstCol).Copy Worksheets("Sheet2").Range("A1")

    Exit Sub

errhandler:
    MsgBox "No Cells containing specified text found"
   
End Sub

I am really sorry!!!

It needs to be from bottom to top? So how would I do this? I've tried before but it does not work. Really thank you so much for the help so far

anon500 · Jul 22, 2020

Please ignore my first part of that above post, I do not know how to edit

MARK858 · Jul 22, 2020

anon500 said:
It needs to be from bottom to top? So how would I do this?

No idea what you are asking for.

Searching a row A for 2 values - how to extend to Row U and copy + paste those to new sheet?

anon500

New Member

Excel Facts

MARK858

MrExcel MVP

anon500

New Member

MARK858

MrExcel MVP

anon500

New Member

MARK858

MrExcel MVP

MARK858

MrExcel MVP

anon500

New Member

anon500

New Member

MARK858

MrExcel MVP

Similar threads

Forum statistics

Share this page

Searching a row A for 2 values - how to extend to Row U and copy + paste those to new sheet?

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

New Member

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock