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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
283
etaf
etaf
D
  • Question Question
Compile error: Wrong number of arguments or invalid property assignment
Replies
2
Views
71
daveyc18
D
R
  • Question Question
Move row to bottom of range is cell value is zero
Replies
6
Views
297
Peter_SSs
Peter_SSs
A
  • Solved
Lookup PAAF ID based on Worker ID and date range
Replies
3
Views
123
Fluff
Fluff
H
  • Solved
First cell with a value of 0 after a certain row
2
Replies
10
Views
261
Fluff
Fluff

Forum statistics

Threads
1,223,909
Messages
6,175,315
Members
452,634
Latest member
cpostell

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