lock autofilter range or autofilter part of range

[werafa]

Hi guys,

I am trying to autofilter (using vba) a table and exclude the 'others' and 'totals' rows, located at bottom of table. I have set the autofilter over the range I want (with or without the extra rows being present), and excel helpfully expands the range to cover the extra rows.

how can I stop this behavior and lock the range to the one I specify (this works if I do it manually, but not if I use 'selection' i.e.

[VBA]mySheet.Range("A10:M" & numRows + 10).Select
Selection.AutoFilter 'set Filter field on table range[/VBA]

ends up pointing to A10:M22 instead of A10:M20 (numrows = 10 in this case)

thanks
Tim

[werafa]

I have had a bit more of a play doing this manually, and the manual process replicates vba perfectly

Specify a range (with additional adjoining rows).
Add autofilter
sort using autofilter. (the additional rows are included in the autofilter sort here)
Hit undo several times - and here you can see that the selected range changes between undo sort and undo selection. Tis nearly enough to make a man swear........ especially as the autofilter range.address returns as a locked range (with the $ symbols)

ps. am using office 2010

[sassora]

How about attaching the file

[werafa]

Hi Sassora
Tis not so easy to post the table, but:

I create a table that shows a list such as

1: Col.1 items; Col.2 Items; Col.3 items; etc
2: Col.1 items; Col.2 Items; Col.3 items; etc
3: Col.1 items; Col.2 Items; Col.3 items; etc
Others: Col.1 items; Col.2 Items; Col.3 items; etc
Totals: Col.1 items; Col.2 Items; Col.3 items; etc

I want to put the autofilter on rows 1 to three, so I can sort by the various columns. This works if I put a blank row between row 3 and row others. It doesn't work if there is no blank row as I cannot stop excel from expanding the range that I specify and including the adjoining data.

This appears to be intended behavior by the excel developers

I have reinstated the blank row, but this has other tradeoffs in the sheet that i am developing, and I would love to figure out how to override the behavior

Tim

[sassora]

In Excel 2010, if you select the data you want to sort then use the sort dialog box to tweak the details then the recorded VBA code looks somewhat like this:

[VBA] Range("A1:C3").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A3"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:C3")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With[/VBA]

As you can see, you should only select the items you want to sort. Play with this code and see if you can make it do what you want (assuming you're not using xl2003).

[werafa]

Hi Sassora,

This is what I have done, and how I did get my initial code. my problem is that I cannot stop excel from ignoring my specification of A1:C3, and instead inserting A1:C5, where there is an additional 2 rows of data (that I wish to exclude).

Doing this manually repeats the behavior, and using undo (CTRL-Z) shows an additional hidden step where excel expands the selected range.

I am using excel 2010

[sassora]

Have you tried using a named range?