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