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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
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,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

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