Finding first and last value in a cell to match a set value and selecting set ra

Question

Finding first and last value in a cell to match a set value and selecting set range of columns and pasting them into another range in the same worksheet

I need my macro code to search down a column of values and select the rows that hold the same value as the required one. I know that the columns are already in the order I need but I do not know how to select the required range of all values that correspond to the set value that I want to match. (sorry if that is a bit confusing, I am not sure how to explain it better but say below is the columns in my excel worksheet and I want to select rows in columns A:C when 1 is the required number in column B. I want to know how to do this using VBA coding

A B C

12 1 3

45 1 2

16 2 5

2 2 8

The simpler the better as I have had a lot of problems with my code not making sense...

Thank you

Explanation / Answer

../,Try this code.:

Sub selecter()
Dim lastrow As Long, ReqNo As Long
Dim MyRange As Range, c As Range
Dim CopyRange As Range
ReqNo = Application.InputBox("Enter desired number", Type:=1)
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Set MyRange = Range("B1:B" & lastrow)
For Each c In MyRange
    If c.Value = ReqNo Then
    If CopyRange Is Nothing Then
        Set CopyRange = c.Offset(, -1).Resize(, 3)
    Else
        Set CopyRange = Union(CopyRange, c.Offset(, -1).Resize(, 3))
    End If
    End If
Next
If Not CopyRange Is Nothing Then
    CopyRange.Select
End If
End Sub

========

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.