Hi Excel Gurus,
Can anyone spot the reason my code is selecting a wider range than is requested?
I believe I have all action contained to just column D yet when the sub ends I end up with Range ("D1:I*") selected and highlighted in gray. The * represents a dynamic number than changes depending on the number of cells containing data.
The code ask for the user to enter a Client Name and adds this data to the next available cell in column D. Column D is then sorted alphabetically. One of the last lines of code sets the range as ("D1:D" & rng2).
Sub test3()
'
Dim rng As Integer
Dim rng2 As Integer
Dim myViewName As String
' user enters client name.
myViewName = InputBox("Enter Client Name")
' rng is the last row containing existing client names
Range("D1").Select
Range(Selection, Selection.End(xlDown)).Select
rng = Selection.Rows.Count
' move to next cell down from last row and pastes Client Name in cell
Range("D" & (rng + 1)).Select
ActiveCell.Value = (myViewName)
' rng2 is the new count for last row used
Range("D1").Select
Range(Selection, Selection.End(xlDown)).Select
rng2 = Selection.Rows.Count
' Below sorts column D alphabetically
Range("D1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.Worksheets("VBATest").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("VBATest").Sort.SortFields.Add Key:=Range("D1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("VBATest").Sort
.SetRange Range("D1:D" & rng2)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Can anyone spot the reason my code is selecting a wider range than is requested?
I believe I have all action contained to just column D yet when the sub ends I end up with Range ("D1:I*") selected and highlighted in gray. The * represents a dynamic number than changes depending on the number of cells containing data.
The code ask for the user to enter a Client Name and adds this data to the next available cell in column D. Column D is then sorted alphabetically. One of the last lines of code sets the range as ("D1:D" & rng2).
Sub test3()
'
Dim rng As Integer
Dim rng2 As Integer
Dim myViewName As String
' user enters client name.
myViewName = InputBox("Enter Client Name")
' rng is the last row containing existing client names
Range("D1").Select
Range(Selection, Selection.End(xlDown)).Select
rng = Selection.Rows.Count
' move to next cell down from last row and pastes Client Name in cell
Range("D" & (rng + 1)).Select
ActiveCell.Value = (myViewName)
' rng2 is the new count for last row used
Range("D1").Select
Range(Selection, Selection.End(xlDown)).Select
rng2 = Selection.Rows.Count
' Below sorts column D alphabetically
Range("D1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.Worksheets("VBATest").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("VBATest").Sort.SortFields.Add Key:=Range("D1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("VBATest").Sort
.SetRange Range("D1:D" & rng2)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub