Find and Select Rows

[pwill]

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

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Range	Ref	A	B	C	D	E	F	G
2	0	1 - 5	5	0	0	0	0	0	0	0
3	0	6 - 10	5	1	1	1	1	1	1	1
4	0	11 - 15	5	2	2	2	2	2	2	2
5	0	1 - 10	10	3	3	3	3	3	3	3
6	0	11 - 20	10	4	4	4	4	4	4	4
7	0	21 - 30	10	5	5	5	5	5	5	5
8	0	1 - 15	15	6	6	6	6	6	6	6
9	0	16 - 30	15	7	7	7	7	7	7	7
10	0	31 - 45	15	8	8	8	8	8	8	8
11	1	1 - 5	5	0	0	0	0	0	0	0
12	1	6 - 10	5	1	1	1	1	1	1	1
13	1	11 - 15	5	2	2	2	2	2	2	2
14	1	1 - 10	10	3	3	3	3	3	3	3
15	1	11 - 20	10	4	4	4	4	4	4	4
16	1	21 - 30	10	5	5	5	5	5	5	5
17	1	1 - 15	15	6	6	6	6	6	6	6
18	1	16 - 30	15	7	7	7	7	7	7	7
19	1	31 - 45	15	8	8	8	8	8	8	8
20
21

<tbody>
</tbody>

Any help would be appreciated

Regards

pwill

 

[mrshl9898]

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.

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

 

[pwill]

Thanks marshl9898

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

many thanks,

much appreciated

pwill

 

[pwill]

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.

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

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

 

[mrshl9898]

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.

 

[mrshl9898]

Also is Sheet3 and Sheet7 the name or index number?

 

[pwill]

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

 

[pwill]

Sheet 3 I have named Data and Sheet7 I have named PrdRes

regards

 

[mrshl9898]

Try

[COLOR=#333333]Sheets(3).select[/COLOR]
[COLOR=#333333]Range("A3").PasteSpecial xlPasteValues
[/COLOR]

 

[pwill]

Hi,

It still Debugs with

Range("A3").PasteSpecial xlPasteValues

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

regards