Selecting data using VBA based on a cell criteria

[CharlesN]

Hi there

I was wondering if the following can be done using VBA?

I have a spreadsheet with rows of data sorted by warehouse branches in column C. What I would like to do is select the rows of a certain warehouse branch based on the current warehouse branch based on where the cell I'm on.

So for example if the current cell activated in column C is on "London" for example, I would like to run a macro that selects all the rows below and above that are the London branch.

My spreadsheet is sorted by branch.

Thanks in advance!

 

[VoG]

Hello and welcome to MrExcel.

Try this:

Sub FindWH()
Dim tempcell As Range, Found As Range, sTxt, FoundRange As Range
Set Found = Range("C1")
sTxt = ActiveCell.Value
If sTxt = "" Then Exit Sub
Set tempcell = Columns("C").Find(What:=sTxt, After:=Found)
If tempcell Is Nothing Then
    MsgBox "Not found", vbExclamation
    Exit Sub
Else
    Set Found = tempcell
    Set FoundRange = Found
End If
Do
    Set tempcell = Cells.FindNext(After:=Found)
    If Found.Row >= tempcell.Row Then Exit Do
    Set Found = tempcell
    Set FoundRange = Application.Union(FoundRange, Found)
Loop
FoundRange.Select
End Sub

 

[Fazza]

Hi. FYI this can be also done without code. Please search for info on "parameterised query tables". Regards, Fazza