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

anon500

New Member
Joined
Jul 17, 2020
Messages
11
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!
 
Last edited by a moderator:

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,834
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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) :unsure:

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

Book1
ABC
1SP NameOrder IDOrder Date
2Andrew Fuller108-14-N3114/01/2014
3Margaret Peacock102-26-N8926/08/2012
4Test1109-19-F6019/03/2014
5Anne Dodsworth107-16-N0716/10/2013
6Robert King107-19-F8619/12/2013
7Laura Callahan109-17-N5617/03/2014
8Laura Callahan104-20-N8120/03/2013
9Laura Callahan105-26-N4826/05/2013
10Nancy Davolio104-1-F9201/04/2013
11Anne Dodsworth107-23-F1523/10/2013
12Margaret Peacock109-20-N6520/03/2014
13Nancy Davolio109-26-F1326/02/2014
14Robert King110-28-F5428/04/2014
15Nancy Davolio107-10-F0010/10/2013
16Test2106-9-F9809/10/2013
17Margaret Peacock107-19-N4619/11/2013
18Laura Callahan16/01/2014
19Michael Suyama
20Janet Leverling
21Nancy Davolio
Dump
 
Last edited:

anon500

New Member
Joined
Jul 17, 2020
Messages
11
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) :unsure:

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

Book1
ABC
1SP NameOrder IDOrder Date
2Andrew Fuller108-14-N3114/01/2014
3Margaret Peacock102-26-N8926/08/2012
4Test1109-19-F6019/03/2014
5Anne Dodsworth107-16-N0716/10/2013
6Robert King107-19-F8619/12/2013
7Laura Callahan109-17-N5617/03/2014
8Laura Callahan104-20-N8120/03/2013
9Laura Callahan105-26-N4826/05/2013
10Nancy Davolio104-1-F9201/04/2013
11Anne Dodsworth107-23-F1523/10/2013
12Margaret Peacock109-20-N6520/03/2014
13Nancy Davolio109-26-F1326/02/2014
14Robert King110-28-F5428/04/2014
15Nancy Davolio107-10-F0010/10/2013
16Test2106-9-F9809/10/2013
17Margaret Peacock107-19-N4619/11/2013
18Laura Callahan16/01/2014
19Michael Suyama
20Janet Leverling
21Nancy 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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,834
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

New Member
Joined
Jul 17, 2020
Messages
11

ADVERTISEMENT

Anyone please?
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,834
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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?
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,834
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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

New Member
Joined
Jul 17, 2020
Messages
11
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) :unsure:

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

Book1
ABC
1SP NameOrder IDOrder Date
2Andrew Fuller108-14-N3114/01/2014
3Margaret Peacock102-26-N8926/08/2012
4Test1109-19-F6019/03/2014
5Anne Dodsworth107-16-N0716/10/2013
6Robert King107-19-F8619/12/2013
7Laura Callahan109-17-N5617/03/2014
8Laura Callahan104-20-N8120/03/2013
9Laura Callahan105-26-N4826/05/2013
10Nancy Davolio104-1-F9201/04/2013
11Anne Dodsworth107-23-F1523/10/2013
12Margaret Peacock109-20-N6520/03/2014
13Nancy Davolio109-26-F1326/02/2014
14Robert King110-28-F5428/04/2014
15Nancy Davolio107-10-F0010/10/2013
16Test2106-9-F9809/10/2013
17Margaret Peacock107-19-N4619/11/2013
18Laura Callahan16/01/2014
19Michael Suyama
20Janet Leverling
21Nancy 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)
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

New Member
Joined
Jul 17, 2020
Messages
11
Please ignore my first part of that above post, I do not know how to edit
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top