Macro to auto Name a Range based on criteria
Hello,
This is somewhat complicated, so I will try to explain best I can. I have a data worksheet is a wb that is update weekly and new data is dumped on top of it. In the data, there are 250 rows with 6 columns. Column B has various departments and Column C has the positions that go with the department. For certain validation criteria, I need to name the range of only the positions (col C) that are in each department. So the data looks like this: Front Desk Agent Front Desk Supervisor Front Desk Asst Mgr Front Desk Mgr Housekeeping Housekeeper Housekeeping Houseman etc...etc...etc.. So, I would like to be able to run a macro and it would redefine the ranges for Front Desk and only use the positions. Then go on to housekeeping and so on. Any assistance would be gratefull. |
Macro to auto Name a Range based on criteria
Give this a go:
Sub NameThem() Dim lRow As Long Dim counter As Long Dim Dept As String Dim newDept As String Dim sRow As Integer Dim eRow As Integer Dim chk As Range Dim colB As Range lRow = Cells(Rows.Count, 2).End(xlUp).Row + 1 Set colB = Range(Cells(2, 2), Cells(lRow, 2)) Dept = Trim(Cells(2, 2).Value) newDept = Replace(Dept, " ", "") sRow = 2 eRow = 2 For Each chk In colB If chk.Value < Dept Then ActiveWorkbook.Names.Add Name:=newDept, _ RefersToR1C1:="=Sheet2!R" & sRow & "C3:R" & eRow & "C3" Dept = chk.Value newDept = Replace(Dept, " ", "") sRow = chk.Row eRow = chk.Row Else eRow = chk.Row End If Next chk End Sub Hope this helps Rowan "Mike R." wrote: Hello, This is somewhat complicated, so I will try to explain best I can. I have a data worksheet is a wb that is update weekly and new data is dumped on top of it. In the data, there are 250 rows with 6 columns. Column B has various departments and Column C has the positions that go with the department. For certain validation criteria, I need to name the range of only the positions (col C) that are in each department. So the data looks like this: Front Desk Agent Front Desk Supervisor Front Desk Asst Mgr Front Desk Mgr Housekeeping Housekeeper Housekeeping Houseman etc...etc...etc.. So, I would like to be able to run a macro and it would redefine the ranges for Front Desk and only use the positions. Then go on to housekeeping and so on. Any assistance would be gratefull. |
Macro to auto Name a Range based on criteria
PERFECT!!! Thanks!
Mike "Rowan" wrote: Give this a go: Sub NameThem() Dim lRow As Long Dim counter As Long Dim Dept As String Dim newDept As String Dim sRow As Integer Dim eRow As Integer Dim chk As Range Dim colB As Range lRow = Cells(Rows.Count, 2).End(xlUp).Row + 1 Set colB = Range(Cells(2, 2), Cells(lRow, 2)) Dept = Trim(Cells(2, 2).Value) newDept = Replace(Dept, " ", "") sRow = 2 eRow = 2 For Each chk In colB If chk.Value < Dept Then ActiveWorkbook.Names.Add Name:=newDept, _ RefersToR1C1:="=Sheet2!R" & sRow & "C3:R" & eRow & "C3" Dept = chk.Value newDept = Replace(Dept, " ", "") sRow = chk.Row eRow = chk.Row Else eRow = chk.Row End If Next chk End Sub Hope this helps Rowan "Mike R." wrote: Hello, This is somewhat complicated, so I will try to explain best I can. I have a data worksheet is a wb that is update weekly and new data is dumped on top of it. In the data, there are 250 rows with 6 columns. Column B has various departments and Column C has the positions that go with the department. For certain validation criteria, I need to name the range of only the positions (col C) that are in each department. So the data looks like this: Front Desk Agent Front Desk Supervisor Front Desk Asst Mgr Front Desk Mgr Housekeeping Housekeeper Housekeeping Houseman etc...etc...etc.. So, I would like to be able to run a macro and it would redefine the ranges for Front Desk and only use the positions. Then go on to housekeeping and so on. Any assistance would be gratefull. |
All times are GMT +1. The time now is 04:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com