Select a range
 

I've included some code below that selects all the used
cells in Column C, then prompts for the current month and
enters that month in the selected range. However, if a
user adds a new row, they'd key a line number in Column B
and would use the macro to fill in the months, so the
selected range would be one row short. What I really need
is to modify the current macro to select the used cells in
Column B, then reselect the same number of cells in Column
C to include the new blank cell. Any ideas? TIA

Dim rng As Range
Dim topcell As Range
Dim botcell As Range
With Worksheets("Sheet1")
Set topcell = .Range("C2")
Set botcell = .Cells(.Rows.Count, topcell.Column).End
(xlUp)
Set rng = Range(topcell, botcell)

End With
rng.Select
Selection = InputBox(Prompt:="What month?")

Select a range
 

Dim rng As Range
With Worksheets("Sheet1")
set rng = .Range(.Cells(2,2), _
.Cells(rows.count,2).End(xlup)).Offset(0,1)
End With
rng.Select
Selection = InputBox(Prompt:="What month?")

--
Regards,
Tom Ogilvy

"Eva Shanley" wrote in message
...
I've included some code below that selects all the used
cells in Column C, then prompts for the current month and
enters that month in the selected range. However, if a
user adds a new row, they'd key a line number in Column B
and would use the macro to fill in the months, so the
selected range would be one row short. What I really need
is to modify the current macro to select the used cells in
Column B, then reselect the same number of cells in Column
C to include the new blank cell. Any ideas? TIA

Dim rng As Range
Dim topcell As Range
Dim botcell As Range
With Worksheets("Sheet1")
Set topcell = .Range("C2")
Set botcell = .Cells(.Rows.Count, topcell.Column).End
(xlUp)
Set rng = Range(topcell, botcell)

End With
rng.Select
Selection = InputBox(Prompt:="What month?")

Select a range
 

Eva,

Use the count of column B [correct for word wrap].

Dim rng As Range
Dim topcell As Range
Dim botcell As Range
With Worksheets("Sheet1")
Set topcell = .Range("C2")
Set botcell = .Cells(.Cells(.Rows.Count, 2).End(xlUp).Row,
topcell.Column)
Set rng = Range(topcell, botcell)

End With
rng.Select
Selection = InputBox(Prompt:="What month?")

--
sb
"Eva Shanley" wrote in message
...
I've included some code below that selects all the used
cells in Column C, then prompts for the current month and
enters that month in the selected range. However, if a
user adds a new row, they'd key a line number in Column B
and would use the macro to fill in the months, so the
selected range would be one row short. What I really need
is to modify the current macro to select the used cells in
Column B, then reselect the same number of cells in Column
C to include the new blank cell. Any ideas? TIA

Dim rng As Range
Dim topcell As Range
Dim botcell As Range
With Worksheets("Sheet1")
Set topcell = .Range("C2")
Set botcell = .Cells(.Rows.Count, topcell.Column).End
(xlUp)
Set rng = Range(topcell, botcell)

End With
rng.Select
Selection = InputBox(Prompt:="What month?")

Select a range
 

Thanks to both Tom and Steve for the help; works great.
-----Original Message-----
I've included some code below that selects all the used
cells in Column C, then prompts for the current month and
enters that month in the selected range. However, if a
user adds a new row, they'd key a line number in Column B
and would use the macro to fill in the months, so the
selected range would be one row short. What I really need
is to modify the current macro to select the used cells
in
Column B, then reselect the same number of cells in
Column
C to include the new blank cell. Any ideas? TIA

Dim rng As Range
Dim topcell As Range
Dim botcell As Range
With Worksheets("Sheet1")
Set topcell = .Range("C2")
Set botcell = .Cells(.Rows.Count, topcell.Column).End
(xlUp)
Set rng = Range(topcell, botcell)

End With
rng.Select
Selection = InputBox(Prompt:="What month?")
.

Select a range
 

Eva,

I liked Tom's solution better than my own!

But that what makes Tom, Tom...

--
sb
"Eva Shanley" wrote in message
...
Thanks to both Tom and Steve for the help; works great.
-----Original Message-----
I've included some code below that selects all the used
cells in Column C, then prompts for the current month and
enters that month in the selected range. However, if a
user adds a new row, they'd key a line number in Column B
and would use the macro to fill in the months, so the
selected range would be one row short. What I really need
is to modify the current macro to select the used cells
in
Column B, then reselect the same number of cells in
Column
C to include the new blank cell. Any ideas? TIA

Dim rng As Range
Dim topcell As Range
Dim botcell As Range
With Worksheets("Sheet1")
Set topcell = .Range("C2")
Set botcell = .Cells(.Rows.Count, topcell.Column).End
(xlUp)
Set rng = Range(topcell, botcell)

End With
rng.Select
Selection = InputBox(Prompt:="What month?")
.