Select a range

  • Thread starter Thread starter Eva Shanley
  • Start date Start date
E

Eva Shanley

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?")
 
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?")
 
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?")
 
Back
Top