Problem with sort through VBA

**Bob Berg** · 11-21-2012, 11:07 AM

I am trying to sort the values in rows 3 through the end of the column in a worksheet (named "parms") using VBA:

lastline = Range("parms!I" & Rows.Count).End(xlUp).Row ' the last line in I that has data
Range("parms!I3:I" & lastline).Sort Range("parms!I3:I" & lastline)

It seems to work fine when lastline > 3 (i.e., it properly sorts I3 through the end of the column), but when lastline = 3, it goes rogue and not only sorts I1:I3 instead of I3:I3 but also sorts H1:H3 and G1:G3. What the heck is going on?

**shg** · 11-21-2012, 11:13 AM

You can test lastline before sorting.

**Bob Berg** · 11-21-2012, 11:35 AM

Thanks, shg. I surely can test before sorting. But I'm new to this whole VBA/Excel thing, and it makes me nervous that it would display such seemingly random and irrational behavior. Is it thinking something along the lines of: "He can't really want me to sort I3:I3. What kind of fool would want to sort one value? He must want to sort I1:I3, H1:H3, and G1:G3 instead."?

**shg** · 11-21-2012, 01:12 PM

I think that behavior has changed since Excel 2003; it used to generate a run-time error if you tried to sort one cell (or one row if sorting top to bottom).

Now the behavior is akin the the way Excel behaves under other circumstances when only a single cell is selected, and the sort is applied to the current region of the cell. Makes sense to me.

**Bob Berg** · 11-21-2012, 01:40 PM

Interesting! I would never have guessed exactly what Excel would do...only that it would be different from what I'd expect (actually, I expect it to do what I expect it to. But when it doesn't, it isn't a big surprise.)