Excel Error Message

[Rich]

Hello

I have a large spreadsheet with AutoFilter set. I have
filtered to a specific item and want to delete the rows
that have been filtered. When I highlight the rows and
Delete, the following error appears -

Microsoft Excel cannot create or use the data range
reference because it is too complex. try one or more of
the following -

- Use data that can be selected in one contiguous
rectangle.
- Use data from the same sheet.

I have had a look in knowledge base but can't find
anything. Anyone got any idea's??

Thanks
Rich

 

[Debra Dalgleish]

It would require more steps, but you could:

--filter the table
--mark the visible rows with an "x"
--sort by the column that contains the "x"
--Select and delete the marked rows

 

[Dave Peterson]

http://support.microsoft.com/?kbid=832293
The Excel VBA function ".SpecialCells(xlCellTypeBlanks)" does not
work as expected

(but the xlcelltypeblanks was too specific.)

Maybe you could add a column with the existing row numbers in them. Put 1 in
the top row, 2 in the second row and select both those cells and drag down.

Now sort by that column, then apply your filter, delete the rows, and then
resort by that helper column and then delete that helper column.

whew! It's actually pretty fast once you do it a couple of times.