I am trying to determine VBA code that would allow me to:

1. Find the start and end of ranges of data that are separated by several
empty empty rows. Each seperate range is a region of information that will
be treated differently in subsequent code and formulas. I have the VBA code
insert 20 blank rows at the top of the page for later use to insert formulas
summarizing data calculations.
2. For the data regions, I need to name the ranges by column and region.
Each column contains the same type of data but each region represents a group
of the data type. The challenge is that the number of rows in each range
varies each day that I will run the code. So I have to define the range
names by the variable start and end row numbers of each region. I need the
number of rows scalable from 1 to at least 500 each.
3. I want to delete the empty rows between the ranges (i.e. put the ranges
regions together) and name the new continuous region by column from its first
row to its last row.
4. I want to be able to force a SUMPRODUCT formula using the range names
into selected cells at the top of the spreadsheet. I know the formula I want
pasted into each selected cell, I just don't know the VBA code required to
paste a formula in the cell. Can you give me an example line with an example
formula?

The preceding is asking alot but I've done a little VBA coding already that
gets close but not quite there. Any suggestions for any one of the above is
appreciated.

Try this, able to name ranges (6,1) would be start of range
you can control how far up/down or left/right it goes by adding numbers
after rows.count or column.count

Range("A6").Select
Set RangeToCheck = ActiveCell.CurrentRegion
Set NewRange = Range(RangeToCheck.Cells(6, 1), _
RangeToCheck.Cells(RangeToCheck.Rows.Count, _
RangeToCheck.Columns.Count))
ActiveWorkbook.Names.Add Name:="AllData", RefersToR1C1:=NewRange
--
Helping Is always a good thing


"BJTex" wrote:

I am trying to determine VBA code that would allow me to:

1. Find the start and end of ranges of data that are separated by several
empty empty rows. Each seperate range is a region of information that will
be treated differently in subsequent code and formulas. I have the VBA code
insert 20 blank rows at the top of the page for later use to insert formulas
summarizing data calculations.
2. For the data regions, I need to name the ranges by column and region.
Each column contains the same type of data but each region represents a group
of the data type. The challenge is that the number of rows in each range
varies each day that I will run the code. So I have to define the range
names by the variable start and end row numbers of each region. I need the
number of rows scalable from 1 to at least 500 each.
3. I want to delete the empty rows between the ranges (i.e. put the ranges
regions together) and name the new continuous region by column from its first
row to its last row.
4. I want to be able to force a SUMPRODUCT formula using the range names
into selected cells at the top of the spreadsheet. I know the formula I want
pasted into each selected cell, I just don't know the VBA code required to
paste a formula in the cell. Can you give me an example line with an example
formula?

The preceding is asking alot but I've done a little VBA coding already that
gets close but not quite there. Any suggestions for any one of the above is
appreciated.

I am probably missing something in your response. My code finds the start
and end of the first variable range of rows and fixed number of columns. My
primary problem is that I don't know how to then determine the number of
blank rows until the next range of data and then start with the next
non-empty row and column A as my anchor cell for the second variable range of
rows. Once that second range starting row and ending row is known, I can
then use Offset to successively name each needed range of columns to the
right. I just didn't get that solution from your response. Thanks.

"QuietMan" wrote:

Try this, able to name ranges (6,1) would be start of range
you can control how far up/down or left/right it goes by adding numbers
after rows.count or column.count

Range("A6").Select
Set RangeToCheck = ActiveCell.CurrentRegion
Set NewRange = Range(RangeToCheck.Cells(6, 1), _
RangeToCheck.Cells(RangeToCheck.Rows.Count, _
RangeToCheck.Columns.Count))
ActiveWorkbook.Names.Add Name:="AllData", RefersToR1C1:=NewRange
--
Helping Is always a good thing


"BJTex" wrote:

I am trying to determine VBA code that would allow me to:

1. Find the start and end of ranges of data that are separated by several
empty empty rows. Each seperate range is a region of information that will
be treated differently in subsequent code and formulas. I have the VBA code
insert 20 blank rows at the top of the page for later use to insert formulas
summarizing data calculations.
2. For the data regions, I need to name the ranges by column and region.
Each column contains the same type of data but each region represents a group
of the data type. The challenge is that the number of rows in each range
varies each day that I will run the code. So I have to define the range
names by the variable start and end row numbers of each region. I need the
number of rows scalable from 1 to at least 500 each.
3. I want to delete the empty rows between the ranges (i.e. put the ranges
regions together) and name the new continuous region by column from its first
row to its last row.
4. I want to be able to force a SUMPRODUCT formula using the range names
into selected cells at the top of the spreadsheet. I know the formula I want
pasted into each selected cell, I just don't know the VBA code required to
paste a formula in the cell. Can you give me an example line with an example
formula?

The preceding is asking alot but I've done a little VBA coding already that
gets close but not quite there. Any suggestions for any one of the above is
appreciated.