Find and Select Rows

P

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
Hi can anyone help with a macro that I could use with a drop down list that will find and select rows based on Name from column A and the Ref from column C (Sheet7)

IE if I wanted to find 1 as the name from column A and 10 as the Ref in column C in this example A14:J16 would be selected or if I wanted to find 0 as the Name and 15 as the Ref A8:J10 would be selected and so on... and then copy the selected range to Sheet3 A3:J


<tbody>
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, width: 64, align: center"] A [/TD]
[TD="class: xl63, width: 64, align: center"] B [/TD]
[TD="class: xl63, width: 64, align: center"] C [/TD]
[TD="class: xl63, width: 64, align: center"] D [/TD]
[TD="class: xl63, width: 64, align: center"] E [/TD]
[TD="class: xl63, width: 64, align: center"] F [/TD]
[TD="class: xl63, width: 64, align: center"] G [/TD]
[TD="class: xl63, width: 64, align: center"] H [/TD]
[TD="class: xl63, width: 64, align: center"] I [/TD]
[TD="class: xl63, width: 64, align: center"] J [/TD]
[TD="class: xl63, width: 64, align: center"] K [/TD]
[TD="class: xl63, width: 64, align: center"] L [/TD]
[TD="class: xl63, width: 64, align: center"] M [/TD]

[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] Name [/TD]
[TD="class: xl63, align: center"] Range [/TD]
[TD="class: xl63, align: center"] Ref [/TD]
[TD="class: xl63, align: center"] A [/TD]
[TD="class: xl63, align: center"] B [/TD]
[TD="class: xl63, align: center"] C [/TD]
[TD="class: xl63, align: center"] D [/TD]
[TD="class: xl63, align: center"] E [/TD]
[TD="class: xl63, align: center"] F [/TD]
[TD="class: xl63, align: center"] G [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

[TD="class: xl63, align: center"] 2 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl64, align: center"] 1 - 5 [/TD]
[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

[TD="class: xl63, align: center"] 3 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl64, align: center"] 6 - 10 [/TD]
[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

[TD="class: xl63, align: center"] 4 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl65, align: center"] 11 - 15 [/TD]
[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"] 2 [/TD]
[TD="class: xl63, align: center"] 2 [/TD]
[TD="class: xl63, align: center"] 2 [/TD]
[TD="class: xl63, align: center"] 2 [/TD]
[TD="class: xl63, align: center"] 2 [/TD]
[TD="class: xl63, align: center"] 2 [/TD]
[TD="class: xl63, align: center"] 2 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl63, align: center"] 1 - 10 [/TD]
[TD="class: xl63, align: center"] 10 [/TD]
[TD="class: xl63, align: center"] 3 [/TD]
[TD="class: xl63, align: center"] 3 [/TD]
[TD="class: xl63, align: center"] 3 [/TD]
[TD="class: xl63, align: center"] 3 [/TD]
[TD="class: xl63, align: center"] 3 [/TD]
[TD="class: xl63, align: center"] 3 [/TD]
[TD="class: xl63, align: center"] 3 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

[TD="class: xl63, align: center"] 6 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl63, align: center"] 11 - 20 [/TD]
[TD="class: xl63, align: center"] 10 [/TD]
[TD="class: xl63, align: center"] 4 [/TD]
[TD="class: xl63, align: center"] 4 [/TD]
[TD="class: xl63, align: center"] 4 [/TD]
[TD="class: xl63, align: center"] 4 [/TD]
[TD="class: xl63, align: center"] 4 [/TD]
[TD="class: xl63, align: center"] 4 [/TD]
[TD="class: xl63, align: center"] 4 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

[TD="class: xl63, align: center"] 7 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl63, align: center"] 21 - 30 [/TD]
[TD="class: xl63, align: center"] 10 [/TD]
[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

[TD="class: xl63, align: center"] 8 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl66, align: center"] 1 - 15 [/TD]
[TD="class: xl63, align: center"] 15 [/TD]
[TD="class: xl63, align: center"] 6 [/TD]
[TD="class: xl63, align: center"] 6 [/TD]
[TD="class: xl63, align: center"] 6 [/TD]
[TD="class: xl63, align: center"] 6 [/TD]
[TD="class: xl63, align: center"] 6 [/TD]
[TD="class: xl63, align: center"] 6 [/TD]
[TD="class: xl63, align: center"] 6 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

[TD="class: xl63, align: center"] 9 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl63, align: center"] 16 - 30 [/TD]
[TD="class: xl63, align: center"] 15 [/TD]
[TD="class: xl63, align: center"] 7 [/TD]
[TD="class: xl63, align: center"] 7 [/TD]
[TD="class: xl63, align: center"] 7 [/TD]
[TD="class: xl63, align: center"] 7 [/TD]
[TD="class: xl63, align: center"] 7 [/TD]
[TD="class: xl63, align: center"] 7 [/TD]
[TD="class: xl63, align: center"] 7 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

[TD="class: xl63, align: center"] 10 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl63, align: center"] 31 - 45 [/TD]
[TD="class: xl63, align: center"] 15 [/TD]
[TD="class: xl63, align: center"] 8 [/TD]
[TD="class: xl63, align: center"] 8 [/TD]
[TD="class: xl63, align: center"] 8 [/TD]
[TD="class: xl63, align: center"] 8 [/TD]
[TD="class: xl63, align: center"] 8 [/TD]
[TD="class: xl63, align: center"] 8 [/TD]
[TD="class: xl63, align: center"] 8 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

[TD="class: xl63, align: center"] 11 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 1 - 5 [/TD]
[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl63, align: center"] 0 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

[TD="class: xl63, align: center"] 12 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 6 - 10 [/TD]
[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

[TD="class: xl63, align: center"] 13 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 11 - 15 [/TD]
[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"] 2 [/TD]
[TD="class: xl63, align: center"] 2 [/TD]
[TD="class: xl63, align: center"] 2 [/TD]
[TD="class: xl63, align: center"] 2 [/TD]
[TD="class: xl63, align: center"] 2 [/TD]
[TD="class: xl63, align: center"] 2 [/TD]
[TD="class: xl63, align: center"] 2 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

[TD="class: xl63, align: center"] 14 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 1 - 10 [/TD]
[TD="class: xl63, align: center"] 10 [/TD]
[TD="class: xl63, align: center"] 3 [/TD]
[TD="class: xl63, align: center"] 3 [/TD]
[TD="class: xl63, align: center"] 3 [/TD]
[TD="class: xl63, align: center"] 3 [/TD]
[TD="class: xl63, align: center"] 3 [/TD]
[TD="class: xl63, align: center"] 3 [/TD]
[TD="class: xl63, align: center"] 3 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

[TD="class: xl63, align: center"] 15 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 11 - 20 [/TD]
[TD="class: xl63, align: center"] 10 [/TD]
[TD="class: xl63, align: center"] 4 [/TD]
[TD="class: xl63, align: center"] 4 [/TD]
[TD="class: xl63, align: center"] 4 [/TD]
[TD="class: xl63, align: center"] 4 [/TD]
[TD="class: xl63, align: center"] 4 [/TD]
[TD="class: xl63, align: center"] 4 [/TD]
[TD="class: xl63, align: center"] 4 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

[TD="class: xl63, align: center"] 16 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 21 - 30 [/TD]
[TD="class: xl63, align: center"] 10 [/TD]
[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"] 5 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

[TD="class: xl63, align: center"] 17 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 1 - 15 [/TD]
[TD="class: xl63, align: center"] 15 [/TD]
[TD="class: xl63, align: center"] 6 [/TD]
[TD="class: xl63, align: center"] 6 [/TD]
[TD="class: xl63, align: center"] 6 [/TD]
[TD="class: xl63, align: center"] 6 [/TD]
[TD="class: xl63, align: center"] 6 [/TD]
[TD="class: xl63, align: center"] 6 [/TD]
[TD="class: xl63, align: center"] 6 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

[TD="class: xl63, align: center"] 18 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 16 - 30 [/TD]
[TD="class: xl63, align: center"] 15 [/TD]
[TD="class: xl63, align: center"] 7 [/TD]
[TD="class: xl63, align: center"] 7 [/TD]
[TD="class: xl63, align: center"] 7 [/TD]
[TD="class: xl63, align: center"] 7 [/TD]
[TD="class: xl63, align: center"] 7 [/TD]
[TD="class: xl63, align: center"] 7 [/TD]
[TD="class: xl63, align: center"] 7 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

[TD="class: xl63, align: center"] 19 [/TD]
[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl63, align: center"] 31 - 45 [/TD]
[TD="class: xl63, align: center"] 15 [/TD]
[TD="class: xl63, align: center"] 8 [/TD]
[TD="class: xl63, align: center"] 8 [/TD]
[TD="class: xl63, align: center"] 8 [/TD]
[TD="class: xl63, align: center"] 8 [/TD]
[TD="class: xl63, align: center"] 8 [/TD]
[TD="class: xl63, align: center"] 8 [/TD]
[TD="class: xl63, align: center"] 8 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

[TD="class: xl63, align: center"] 20 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

[TD="class: xl63, align: center"] 21 [/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]
[TD="class: xl63, align: center"]
[/TD]

</tbody>

Any help would be appreciated

Regards

pwill
 
Last edited:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Hi,

This is working to select and copy/paste the range, but i'm not sure what to do about the dropdown.

Hope you can adjust to suit your needs.

Code: 
Sub selectrows()


Dim findcolA As Long
Dim findcolC As Long


findcolA = InputBox("Enter Value for Column A")
findcolC = InputBox("Enter Value for Column C")


rownum = 2
Do Until Sheets(7).Cells(rownum, 1) = ""
If Sheets(7).Cells(rownum, 1) = findcolA And Sheets(7).Cells(rownum, 3) = findcolC Then
Startrow = rownum
GoTo FindLastRow
End If
rownum = rownum + 1
Loop




FindLastRow:
Do Until Sheets(7).Cells(rownum, 1) = ""
If Sheets(7).Cells(rownum, 1) = findcolA And Sheets(7).Cells(rownum, 3) = findcolC Then
Lastrow = rownum
End If
rownum = rownum + 1
Loop




Let Copyrange = "A" & Startrow & ":" & "J" & Lastrow
Sheets(7).Range(Copyrange).Copy
Sheets(3).Range("A3").PasteSpecial xlPasteValues


End Sub
 
Last edited:
Upvote 0
Thanks marshl9898

I will give it a try and let you know how I get on

many thanks,

much appreciated :)

pwill
 
Upvote 0
mrshl9898 said:
Hi,

This is working to select and copy/paste the range, but i'm not sure what to do about the dropdown.

Hope you can adjust to suit your needs.

Code: 
Sub selectrows()


Dim findcolA As Long
Dim findcolC As Long


findcolA = InputBox("Enter Value for Column A")
findcolC = InputBox("Enter Value for Column C")


rownum = 2
Do Until Sheets(7).Cells(rownum, 1) = ""
If Sheets(7).Cells(rownum, 1) = findcolA And Sheets(7).Cells(rownum, 3) = findcolC Then
Startrow = rownum
GoTo FindLastRow
End If
rownum = rownum + 1
Loop




FindLastRow:
Do Until Sheets(7).Cells(rownum, 1) = ""
If Sheets(7).Cells(rownum, 1) = findcolA And Sheets(7).Cells(rownum, 3) = findcolC Then
Lastrow = rownum
End If
rownum = rownum + 1
Loop




Let Copyrange = "A" & Startrow & ":" & "J" & Lastrow
Sheets(7).Range(Copyrange).Copy
Sheets(3).Range("A3").PasteSpecial xlPasteValues


End Sub
Click to expand...

Hi marshl9898, I just tried your code but it errors I get 'run time error 1004' Application-defined orbject-defined error

and nothing gets copied to sheet3?

Am I doing something wrong? should I have said there are many more rows than I gave in the example, the Names run 0 1 -1 and there is hundreds of rows for each name and Ref

Reagards

pwill
 
Last edited:
Upvote 0
Which version of excel are you running?

You don't have option explicit above the code do you?

Click debug and let me know which line it stops on please.

It won't matter how many rows there are.
 
Upvote 0
Hi,

No I'm not using Option explicit but I wasn't using a module, I just tried it with a module and it debugs at the line

Sheets(3).Range("A3").PasteSpecial xlPasteValues

regards

pwill
 
Upvote 0
Try

Code: 
[COLOR=#333333]Sheets(3).select[/COLOR]
[COLOR=#333333]Range("A3").PasteSpecial xlPasteValues
[/COLOR]
 
Upvote 0
Hi,

It still Debugs with

Range("A3").PasteSpecial xlPasteValues

Its trying, but just seems to select A3 on Sheet3 and stops?

regards
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
318
etaf
etaf
B
  • Solved
More Efficient Looping
2
Replies
19
Views
244
bhsoundman
B
A
  • Solved
Calculate monthly booked percentage of hotel rooms
2 3
Replies
21
Views
340
arendberg
A
D
  • Question Question
Compile error: Wrong number of arguments or invalid property assignment
Replies
2
Views
74
daveyc18
D
K
  • Solved
Index with Multiple Conditions
Replies
2
Views
110
kivikatz
K

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top