Selecting data using VBA based on a cell criteria

CharlesN

New Member
Joined
Mar 17, 2009
Messages
1
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!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Hello and welcome to MrExcel.

Try this:

Code:
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
 
Upvote 0
Hi. FYI this can be also done without code. Please search for info on "parameterised query tables". Regards, Fazza
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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