Validating that named ranges exist
 

Hello,
I'm here yet again to seek the help of the gurus.

I have this bit of code for a button click on one of my spreadsheets.

Private Sub btnCreateRanges_Click()
Application.ScreenUpdating = False
Dim ordLastRow As Long
Dim revLastRow As Long
Dim revSheet As Worksheet
Dim ordSheet As Worksheet
Set revSheet = Sheets("REVENUE")
Set ordSheet = Sheets("ORDERS")
ordLastRow = ordSheet.UsedRange.Rows.Count
revLastRow = revSheet.UsedRange.Rows.Count
ordSheet.Range("B2:B" & ordLastRow).Name = "OrdersCountry"
ordSheet.Range("D2:D" & ordLastRow).Name = "OrdersYear"
ordSheet.Range("E2:E" & ordLastRow).Name = "OrdersMonth"
ordSheet.Range("F2:F" & ordLastRow).Name = "OrdersOffering"
ordSheet.Range("G2:G" & ordLastRow).Name = "OrdersAmount"
revSheet.Range("B2:B" & revLastRow).Name = "RevenueCountry"
revSheet.Range("D2:D" & revLastRow).Name = "RevenueYear"
revSheet.Range("E2:E" & revLastRow).Name = "RevenueMonth"
revSheet.Range("F2:F" & revLastRow).Name = "RevenueOffering"
revSheet.Range("G2:G" & revLastRow).Name = "RevenueAmount"
Application.ScreenUpdating = True
End Sub

The code figures out the last used row on two worksheets and then creates
named ranges for certain columns. I use those named ranges on other parts of
the Workbook and the number of rows grows every month, so the ranges change
every month.
My question is:
Do I need to implement some sort of validation point to check if the named
ranges exist on the Workbook before setting them again (new button click) ?
If so, is there a way that I can check for all 6 named ranges at once? Or
do I have to validate one by one?

Thanks
Juan Correa

Validating that named ranges exist
 

I'll bet you could answer your own question by a simple trial of adding a
row and running the code

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Juan Correa" wrote in message
...
Hello,
I'm here yet again to seek the help of the gurus.

I have this bit of code for a button click on one of my spreadsheets.

Private Sub btnCreateRanges_Click()
Application.ScreenUpdating = False
Dim ordLastRow As Long
Dim revLastRow As Long
Dim revSheet As Worksheet
Dim ordSheet As Worksheet
Set revSheet = Sheets("REVENUE")
Set ordSheet = Sheets("ORDERS")
ordLastRow = ordSheet.UsedRange.Rows.Count
revLastRow = revSheet.UsedRange.Rows.Count
ordSheet.Range("B2:B" & ordLastRow).Name = "OrdersCountry"
ordSheet.Range("D2:D" & ordLastRow).Name = "OrdersYear"
ordSheet.Range("E2:E" & ordLastRow).Name = "OrdersMonth"
ordSheet.Range("F2:F" & ordLastRow).Name = "OrdersOffering"
ordSheet.Range("G2:G" & ordLastRow).Name = "OrdersAmount"
revSheet.Range("B2:B" & revLastRow).Name = "RevenueCountry"
revSheet.Range("D2:D" & revLastRow).Name = "RevenueYear"
revSheet.Range("E2:E" & revLastRow).Name = "RevenueMonth"
revSheet.Range("F2:F" & revLastRow).Name = "RevenueOffering"
revSheet.Range("G2:G" & revLastRow).Name = "RevenueAmount"
Application.ScreenUpdating = True
End Sub

The code figures out the last used row on two worksheets and then creates
named ranges for certain columns. I use those named ranges on other parts
of
the Workbook and the number of rows grows every month, so the ranges
change
every month.
My question is:
Do I need to implement some sort of validation point to check if the named
ranges exist on the Workbook before setting them again (new button click)
?
If so, is there a way that I can check for all 6 named ranges at once? Or
do I have to validate one by one?

Thanks
Juan Correa

Validating that named ranges exist
 

Hi,

Simply resize the range. I've done one to demonstrate the method

Set revSheet = Sheets("REVENUE")
revLastRow = revSheet.UsedRange.Rows.Count
revSheet.Range("RevenueCountry").Resize(revLastRow , 1).Name = "RevenueCountry"
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Juan Correa" wrote:

Hello,
I'm here yet again to seek the help of the gurus.

I have this bit of code for a button click on one of my spreadsheets.

Private Sub btnCreateRanges_Click()
Application.ScreenUpdating = False
Dim ordLastRow As Long
Dim revLastRow As Long
Dim revSheet As Worksheet
Dim ordSheet As Worksheet
Set revSheet = Sheets("REVENUE")
Set ordSheet = Sheets("ORDERS")
ordLastRow = ordSheet.UsedRange.Rows.Count
revLastRow = revSheet.UsedRange.Rows.Count
ordSheet.Range("B2:B" & ordLastRow).Name = "OrdersCountry"
ordSheet.Range("D2:D" & ordLastRow).Name = "OrdersYear"
ordSheet.Range("E2:E" & ordLastRow).Name = "OrdersMonth"
ordSheet.Range("F2:F" & ordLastRow).Name = "OrdersOffering"
ordSheet.Range("G2:G" & ordLastRow).Name = "OrdersAmount"
revSheet.Range("B2:B" & revLastRow).Name = "RevenueCountry"
revSheet.Range("D2:D" & revLastRow).Name = "RevenueYear"
revSheet.Range("E2:E" & revLastRow).Name = "RevenueMonth"
revSheet.Range("F2:F" & revLastRow).Name = "RevenueOffering"
revSheet.Range("G2:G" & revLastRow).Name = "RevenueAmount"
Application.ScreenUpdating = True
End Sub

The code figures out the last used row on two worksheets and then creates
named ranges for certain columns. I use those named ranges on other parts of
the Workbook and the number of rows grows every month, so the ranges change
every month.
My question is:
Do I need to implement some sort of validation point to check if the named
ranges exist on the Workbook before setting them again (new button click) ?
If so, is there a way that I can check for all 6 named ranges at once? Or
do I have to validate one by one?

Thanks
Juan Correa

Validating that named ranges exist
 

Ahhh...

So I could change things around and have the original code inside a
workbook_open() sub. That way ranges are created when the workbook is
opened; and if the user adds data to the worksheets where the ranges are
located, I could have just one button that resizes the ranges with your
code...

Brilliant.

Thanks a million.
Juan Correa

"Mike H" wrote:

Hi,

Simply resize the range. I've done one to demonstrate the method

Set revSheet = Sheets("REVENUE")
revLastRow = revSheet.UsedRange.Rows.Count
revSheet.Range("RevenueCountry").Resize(revLastRow , 1).Name = "RevenueCountry"
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Juan Correa" wrote:

Hello,
I'm here yet again to seek the help of the gurus.

I have this bit of code for a button click on one of my spreadsheets.

Private Sub btnCreateRanges_Click()
Application.ScreenUpdating = False
Dim ordLastRow As Long
Dim revLastRow As Long
Dim revSheet As Worksheet
Dim ordSheet As Worksheet
Set revSheet = Sheets("REVENUE")
Set ordSheet = Sheets("ORDERS")
ordLastRow = ordSheet.UsedRange.Rows.Count
revLastRow = revSheet.UsedRange.Rows.Count
ordSheet.Range("B2:B" & ordLastRow).Name = "OrdersCountry"
ordSheet.Range("D2:D" & ordLastRow).Name = "OrdersYear"
ordSheet.Range("E2:E" & ordLastRow).Name = "OrdersMonth"
ordSheet.Range("F2:F" & ordLastRow).Name = "OrdersOffering"
ordSheet.Range("G2:G" & ordLastRow).Name = "OrdersAmount"
revSheet.Range("B2:B" & revLastRow).Name = "RevenueCountry"
revSheet.Range("D2:D" & revLastRow).Name = "RevenueYear"
revSheet.Range("E2:E" & revLastRow).Name = "RevenueMonth"
revSheet.Range("F2:F" & revLastRow).Name = "RevenueOffering"
revSheet.Range("G2:G" & revLastRow).Name = "RevenueAmount"
Application.ScreenUpdating = True
End Sub

The code figures out the last used row on two worksheets and then creates
named ranges for certain columns. I use those named ranges on other parts of
the Workbook and the number of rows grows every month, so the ranges change
every month.
My question is:
Do I need to implement some sort of validation point to check if the named
ranges exist on the Workbook before setting them again (new button click) ?
If so, is there a way that I can check for all 6 named ranges at once? Or
do I have to validate one by one?

Thanks
Juan Correa

Validating that named ranges exist
 

I guess that is true Don...
I already knew that if I added data and clicked on the CreateRanges button
the named ranges would just be replaced by new ones with the new addresses.
I guess I should have been more specific on my question...
The reason I wanted to be able to maybe gather a list of existing named
ranges was so that I could make sure the ranges existed before implementing
code to delete them like this:
ActiveWorkbook.Names("OrdersCountry").Delete
Since that will error out if the named range "OrderCountry" does not exist
on the active workbook.
But now that Mike has shown me that tip with the Resizing of the ranges,
I'll just get rid of the whole deleting code all together.

Cheers
Juan Correa

"Don Guillett" wrote:

I'll bet you could answer your own question by a simple trial of adding a
row and running the code

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Juan Correa" wrote in message
...
Hello,
I'm here yet again to seek the help of the gurus.

I have this bit of code for a button click on one of my spreadsheets.

Private Sub btnCreateRanges_Click()
Application.ScreenUpdating = False
Dim ordLastRow As Long
Dim revLastRow As Long
Dim revSheet As Worksheet
Dim ordSheet As Worksheet
Set revSheet = Sheets("REVENUE")
Set ordSheet = Sheets("ORDERS")
ordLastRow = ordSheet.UsedRange.Rows.Count
revLastRow = revSheet.UsedRange.Rows.Count
ordSheet.Range("B2:B" & ordLastRow).Name = "OrdersCountry"
ordSheet.Range("D2:D" & ordLastRow).Name = "OrdersYear"
ordSheet.Range("E2:E" & ordLastRow).Name = "OrdersMonth"
ordSheet.Range("F2:F" & ordLastRow).Name = "OrdersOffering"
ordSheet.Range("G2:G" & ordLastRow).Name = "OrdersAmount"
revSheet.Range("B2:B" & revLastRow).Name = "RevenueCountry"
revSheet.Range("D2:D" & revLastRow).Name = "RevenueYear"
revSheet.Range("E2:E" & revLastRow).Name = "RevenueMonth"
revSheet.Range("F2:F" & revLastRow).Name = "RevenueOffering"
revSheet.Range("G2:G" & revLastRow).Name = "RevenueAmount"
Application.ScreenUpdating = True
End Sub

The code figures out the last used row on two worksheets and then creates
named ranges for certain columns. I use those named ranges on other parts
of
the Workbook and the number of rows grows every month, so the ranges
change
every month.
My question is:
Do I need to implement some sort of validation point to check if the named
ranges exist on the Workbook before setting them again (new button click)
?
If so, is there a way that I can check for all 6 named ranges at once? Or
do I have to validate one by one?

Thanks
Juan Correa

.

Validating that named ranges exist
 

Seems easier just to re-create...........

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Juan Correa" wrote in message
...
I guess that is true Don...
I already knew that if I added data and clicked on the CreateRanges button
the named ranges would just be replaced by new ones with the new
addresses.
I guess I should have been more specific on my question...
The reason I wanted to be able to maybe gather a list of existing named
ranges was so that I could make sure the ranges existed before
implementing
code to delete them like this:
ActiveWorkbook.Names("OrdersCountry").Delete
Since that will error out if the named range "OrderCountry" does not exist
on the active workbook.
But now that Mike has shown me that tip with the Resizing of the ranges,
I'll just get rid of the whole deleting code all together.

Cheers
Juan Correa

"Don Guillett" wrote:

I'll bet you could answer your own question by a simple trial of adding a
row and running the code

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Juan Correa" wrote in message
...
Hello,
I'm here yet again to seek the help of the gurus.

I have this bit of code for a button click on one of my spreadsheets.

Private Sub btnCreateRanges_Click()
Application.ScreenUpdating = False
Dim ordLastRow As Long
Dim revLastRow As Long
Dim revSheet As Worksheet
Dim ordSheet As Worksheet
Set revSheet = Sheets("REVENUE")
Set ordSheet = Sheets("ORDERS")
ordLastRow = ordSheet.UsedRange.Rows.Count
revLastRow = revSheet.UsedRange.Rows.Count
ordSheet.Range("B2:B" & ordLastRow).Name = "OrdersCountry"
ordSheet.Range("D2:D" & ordLastRow).Name = "OrdersYear"
ordSheet.Range("E2:E" & ordLastRow).Name = "OrdersMonth"
ordSheet.Range("F2:F" & ordLastRow).Name = "OrdersOffering"
ordSheet.Range("G2:G" & ordLastRow).Name = "OrdersAmount"
revSheet.Range("B2:B" & revLastRow).Name = "RevenueCountry"
revSheet.Range("D2:D" & revLastRow).Name = "RevenueYear"
revSheet.Range("E2:E" & revLastRow).Name = "RevenueMonth"
revSheet.Range("F2:F" & revLastRow).Name = "RevenueOffering"
revSheet.Range("G2:G" & revLastRow).Name = "RevenueAmount"
Application.ScreenUpdating = True
End Sub

The code figures out the last used row on two worksheets and then
creates
named ranges for certain columns. I use those named ranges on other
parts
of
the Workbook and the number of rows grows every month, so the ranges
change
every month.
My question is:
Do I need to implement some sort of validation point to check if the
named
ranges exist on the Workbook before setting them again (new button
click)
?
If so, is there a way that I can check for all 6 named ranges at once?
Or
do I have to validate one by one?

Thanks
Juan Correa

.