Selected Range is Larger Than It Should

[Matt_O]

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

 

[yky]

Check the address of ActiveCell.SpecialCells(xlLastCell). It's no longer in Column D

 

[Osvaldo Palmeiro]

Hi.
Try after deleting / commenting these two lines:

Range("D1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

 

[Matt_O]

Check the address of ActiveCell.SpecialCells(xlLastCell). It's no longer in Column D

Hi yky, (and Osvaldo)

Thanks for the prompt reply and catching the problem.

I don't how 'SpecialCells(xlLastCell)' came into being which I guess is/was made up of "D1" and "I" + rng2. I might have copied it from a macro I recorded in order to see the VBA for sorting alphabetically.

I updated the code to Range("D1:D" & rng2).Select and everything is as it should be.

Thanks again. You're responses is very much appreciated.

Matt