I have a spreadsheet that is created from external data. I need to be able to
create a range for all cells matching certain criteria. Not all criteria will
occur every day, and there may be one or many cells containing the same
criteria. For example, Column A contains the Date, and column B contains the
part number. There may be one part number for a given day, or none. I need to
select all cells with part number 111, irregardless of date and create a
named range called 111. I will then create a graph for each named range in my
spreadsheet.

Do you need to create ranges, or do you just need a count of cells that
match your criteria? Perhaps you can set up a separate range where you
enter the criteria, then use formulas to calculate the counts.
Build the charts from that range.

gwhenning wrote:
> I have a spreadsheet that is created from external data. I need to be able to
> create a range for all cells matching certain criteria. Not all criteria will
> occur every day, and there may be one or many cells containing the same
> criteria. For example, Column A contains the Date, and column B contains the
> part number. There may be one part number for a given day, or none. I need to
> select all cells with part number 111, irregardless of date and create a
> named range called 111. I will then create a graph for each named range in my
> spreadsheet.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

maybe something like this.


Sub test()
Dim rng As Range
Dim lLastRow As Long
Dim lRow As Long
Dim iLastColumn As Integer

lLastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
iLastColumn = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column

For lRow = 1 To lLastRow
If Cells(lRow, 2).Value = 111 Then
If rng Is Nothing Then
Set rng = Range(Cells(lRow, 1), Cells(lRow, iLastColumn))
Else
Set rng = Union(rng, Range(Cells(lRow, 1), Cells(lRow,
iLastColumn)))
End If
End If
Next lRow

ThisWorkbook.Names.Add "_111", "=" & rng.Address
Set rng = Nothing

End Sub



--
Hope that helps.

Vergel Adriano


"gwhenning" wrote:

> I have a spreadsheet that is created from external data. I need to be able to
> create a range for all cells matching certain criteria. Not all criteria will
> occur every day, and there may be one or many cells containing the same
> criteria. For example, Column A contains the Date, and column B contains the
> part number. There may be one part number for a given day, or none. I need to
> select all cells with part number 111, irregardless of date and create a
> named range called 111. I will then create a graph for each named range in my
> spreadsheet.

How about an alternative:

Apply data|filter|autofilter to your range
Create a single chart

But change the chart's properties to show just the data on the visible rows.
Select the chart and do:
Tools|Options|Chart Tab|check Plot visible cells only



gwhenning wrote:
>
> I have a spreadsheet that is created from external data. I need to be able to
> create a range for all cells matching certain criteria. Not all criteria will
> occur every day, and there may be one or many cells containing the same
> criteria. For example, Column A contains the Date, and column B contains the
> part number. There may be one part number for a given day, or none. I need to
> select all cells with part number 111, irregardless of date and create a
> named range called 111. I will then create a graph for each named range in my
> spreadsheet.

--

Dave Peterson

I am actually doing a little more than using a filter. Each part number will
have a set of attributes that will need to be graphed on a daily, weekly, or
monthly basis. There are additional columns which I will add to the range, I
just need to know how to select all cells where the value is equal to an
argument I supply, and create a range using those rows, and the columns that
I need.

"Dave Peterson" wrote:

> How about an alternative:
>
> Apply data|filter|autofilter to your range
> Create a single chart
>
> But change the chart's properties to show just the data on the visible rows.
> Select the chart and do:
> Tools|Options|Chart Tab|check Plot visible cells only
>
>
>
> gwhenning wrote:
> >
> > I have a spreadsheet that is created from external data. I need to be able to
> > create a range for all cells matching certain criteria. Not all criteria will
> > occur every day, and there may be one or many cells containing the same
> > criteria. For example, Column A contains the Date, and column B contains the
> > part number. There may be one part number for a given day, or none. I need to
> > select all cells with part number 111, irregardless of date and create a
> > named range called 111. I will then create a graph for each named range in my
> > spreadsheet.
>
> --
>
> Dave Peterson
>

I want to create ranges, because i am going to set the graphs to use each
range as a series.

"Debra Dalgleish" wrote:

> Do you need to create ranges, or do you just need a count of cells that
> match your criteria? Perhaps you can set up a separate range where you
> enter the criteria, then use formulas to calculate the counts.
> Build the charts from that range.
>
> gwhenning wrote:
> > I have a spreadsheet that is created from external data. I need to be able to
> > create a range for all cells matching certain criteria. Not all criteria will
> > occur every day, and there may be one or many cells containing the same
> > criteria. For example, Column A contains the Date, and column B contains the
> > part number. There may be one part number for a given day, or none. I need to
> > select all cells with part number 111, irregardless of date and create a
> > named range called 111. I will then create a graph for each named range in my
> > spreadsheet.
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>
>

You could use data|filter|advanced filter to get a list of unique values.

Then depending on the layout of your data (is it sorted?), you could find the
first and last cell with each value and name that range.

Or loop through all the cells like Vergel suggested.

gwhenning wrote:
>
> I am actually doing a little more than using a filter. Each part number will
> have a set of attributes that will need to be graphed on a daily, weekly, or
> monthly basis. There are additional columns which I will add to the range, I
> just need to know how to select all cells where the value is equal to an
> argument I supply, and create a range using those rows, and the columns that
> I need.
>
> "Dave Peterson" wrote:
>
> > How about an alternative:
> >
> > Apply data|filter|autofilter to your range
> > Create a single chart
> >
> > But change the chart's properties to show just the data on the visible rows.
> > Select the chart and do:
> > Tools|Options|Chart Tab|check Plot visible cells only
> >
> >
> >
> > gwhenning wrote:
> > >
> > > I have a spreadsheet that is created from external data. I need to be able to
> > > create a range for all cells matching certain criteria. Not all criteria will
> > > occur every day, and there may be one or many cells containing the same
> > > criteria. For example, Column A contains the Date, and column B contains the
> > > part number. There may be one part number for a given day, or none. I need to
> > > select all cells with part number 111, irregardless of date and create a
> > > named range called 111. I will then create a graph for each named range in my
> > > spreadsheet.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson