I was hoping somebody could help me with the following.
I need to define a name automatically using a macro similar to the one below, except I need it to name a range of cells not just an individual cell.
So where the following macro names the cell (one row below) when any input is found in row A between 30 & 3000, I need it to name the range of cells between each input in A.
So if I have YY typed in A40 and YY typed in A60 I want the range A41:A59 to be named "Name1" etc..
Hopefully it will not take too much adjustment to the macro below, but unfortunately my macro ability leaves plenty to be desired so any help will be greatly appreciated.
Thanks.
Sub AddingNames()
i = 1
Application.ScreenUpdating = False
Range("a30:a3000").Select
For Each cell In Selection
If ActiveCell <> "" Then
ActiveWorkbook.Names.Add Name:="Name" & i, RefersToR1C1:=ActiveCell.Offset(1, 0)
i = i + 1
End If
ActiveCell.Offset(1, 0).Select
Next cell
Range("a1").Select
Application.ScreenUpdating = True
End Sub
I need to define a name automatically using a macro similar to the one below, except I need it to name a range of cells not just an individual cell.
So where the following macro names the cell (one row below) when any input is found in row A between 30 & 3000, I need it to name the range of cells between each input in A.
So if I have YY typed in A40 and YY typed in A60 I want the range A41:A59 to be named "Name1" etc..
Hopefully it will not take too much adjustment to the macro below, but unfortunately my macro ability leaves plenty to be desired so any help will be greatly appreciated.
Thanks.
Sub AddingNames()
i = 1
Application.ScreenUpdating = False
Range("a30:a3000").Select
For Each cell In Selection
If ActiveCell <> "" Then
ActiveWorkbook.Names.Add Name:="Name" & i, RefersToR1C1:=ActiveCell.Offset(1, 0)
i = i + 1
End If
ActiveCell.Offset(1, 0).Select
Next cell
Range("a1").Select
Application.ScreenUpdating = True
End Sub