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:
Sort by date Sort by votes
Hi,

Its saying you can't paste here because the copy area and paste area arn't the same size?
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Yes it goes to sheet3 but then stops with the debug message

I've just reopened my workbook and going to try again from scratch
 
Upvote 0
Hi mrshl9898

just tried again but it just keeps stopping at sheet3 it selects cell A3

regards
 
Upvote 0
Hi could it be that I have the same Ref value for other names and its trying to select the too?
 
Upvote 0
ok thanks :)

with the drop down I was thinking I could use one column for the name say K1 and L1 for the Ref? but I will try that another day lol
 
Upvote 0
Try this, for some reason using the index number makes cells(2,1) = "".....

That gave me the error you see with the print area.

Naming the sheets fixed it on my end.

(I must point out I don't usually use index numbers so am a bit unfamiliar with the ins and outs.)

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("PrdRes").Cells(rownum, 1).Value = ""
If Sheets("PrdRes").Cells(rownum, 1) = findcolA And Sheets("PrdRes").Cells(rownum, 3) = findcolC Then
Startrow = rownum
GoTo FindLastRow
End If
rownum = rownum + 1
Loop








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








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




End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
319
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
342
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,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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