Selecting Range issue/ nightmare

[kevs14]

Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I get "missing or illegal fields"
when I select ranges in Excel.
If I have first name, last name, email etc
List of 1000.
I miss a dozen last names I cannot select the range?
I have to have a perfectly filled list every dang cell to select a range? thanks!

 

[CyberTaz]

You don't have to have *any* cells filled to select a range. From the
complete lack of descriptive detail about the structure of your data & how
you're trying to select the range there's no way anyone can be sure why
you're getting the error.

If you'd just take the time to provide a clear & accurate description in
your first posting you'll typically get a more immediate solution rather
than having to have folks pull bits & pieces out of you over a period of
several days or more.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[kevs14]

Bob,
it's real simple:
I have a workbook with 5 columns
first name, last name, company, notes. email.

In some of the columns I may have nothing filled in yet or partly filled in.

Excel returns this message:
You have missing or illegal fields", and therefore cannot select a range.

 

[Carl Witthoft]

Bob, <br>
it's real simple: <br>
I have a workbook with 5 columns <br>
first name, last name, company, notes. email. <br><br>In some of the columns
I may have nothing filled in yet or partly filled in. <br><br>Excel returns
this message: <br>
You have missing or illegal fields&quot;, and therefore cannot select a
range.

Do you have any merged cells anywhere in the range?

Check that first, then try copying the first say, 10, rows to a new
workbook and see if you can select the range. If you can, try another
bunch. At some point you'll find the cell with rotten stuff in it.

 

[CyberTaz]

You've still given no indication of *how* you're attempting to select a
range or what range you're attempting to select... We're now into day 2.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[CyberTaz]

Why did it take a day & a half to get you to disclose that you're using the
Advanced Filter??? That's a significant detail, don't you think? "When I
select ranges" doesn't even suggest that the Filter is being used.

In the Advanced Filter dialog in the "Copy To:" field just specify the
reference of the one cell at the upper left corner of the range you want the
filtered records copied to.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[kevs14]

Bob, I use Mactopia and I use email notification.
They have the worst email notification bug of any forum I use.

that's why I'm tardy.

Bob, are you saying instead of using the drag, select your range manually?
Because if that is what you are saying I still get this message if even one cell is blank.

 

[CyberTaz]

Bob, I use Mactopia and I use email notification.
They have the worst email notification bug of any forum I use.

Perhaps so... That's why a newsreader is repeatedly recommended, but that's
another issue altogether.

that's why I'm tardy.

That wasn't my point What I'm saying is that you never disclose any
amount of required details in your initial messages. It takes at least 3-4
rounds of correspondence just to get the info that should have been
disclosed in the first place. Also, if you'd completely & accurately
describe these situations to begin with the 'email notification' issue
becomes far less significant because responders would be better able to
answer your question on the first go-round. All these additional messages
prodding for details not only delay resolution but having to pick out the
individual details from several separate messages prolongs the process even
more by making it more difficult & time consuming to piece the puzzle
together. IOW, your very first message on this subject should have started
with something like "I am using the Advanced Filter from the Data Menu..." &
gone on from there to describe exactly how you were using it as well as the
problem you were having. Most of this information is still missing...

Bob, are you saying instead of using the drag, select your range manually?
Because if that is what you are saying I still get this message if even one
cell is blank.

Because I have no idea where you're encountering the problem I had to guess
& the only way I can reproduce that message is by selecting a range in the
'Copy to:' field of the Advanced Filter dialog. Doing so rarely creates an
accurate matching range, which is what causes the error. So, what I said is:

In the 'Copy to" filed Do Not specify a *range*. Simply type in the cell
reference of the *1* cell at the upper left corner of the area where you
want the found records copied to. The requisite number of columns & rows
will be filled by the found records automatically. IOW, it's very similar to
copy/paste -- You have to select the range to be copied, but when you paste
you just paste to the starting cell rather than selecting a multiple-cell
'range' to paste into.

If my guess is not on the mark you'll need to indicate each and every
setting & reference you're making in the Advanced Filter dialog as well as
exactly when it is that the error message appears. Without knowing all of
the facts there's nothing more I can suggest.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[kevs14]

Bob, I was with the newsreader for 10years, but I love web-based, especially more than anything the email notification. It's working now, but I have to create a new account every few months for it to work. Only Mactopia has this issue.

thanks, I find it very hard to follow this. Don't take it personally. I have the same issue with John M. Your are both excel saints. I respect you a lot. But undersatnd, the layman like me--- it's like talking physics with a plumber.

Bob- question-- forget about the filter menu for a second.
What do I do to just select a range in general. Let's start there.
I stand by that if a cell is blank I seem to always get this message. I thnk I encoutered this same issue in the paste with V Look up fuction. but tell me how to just select a range -- in a basic way that excel will see the range numbes and indicate you properly selected a range. does this exist? this would be a good basic test it I can just select a range in a basic way to test.

 

[CyberTaz]

There are bunches of ways, most of which are described in Excel Help. Search
Help using the term: select cells. Here are some of the common techniques;

1- Click the starting cell, hold Shift & click the ending cell

2- Click in the Name Box of the Formula Bar & type the starting & ending
cell reference separated by a colon (i.e., A10:G50)

3- Drag from start to end of the range

4- Use Edit> Go To & type the range there

5- Select the starting cell, then while holding the Shift key repeatedly
press right arrow to select along the row, down arrow to select more rows

6- Assuming there is content in the range you can also select the first cell
then key Shift+Command+right arrow followed by Shift+Command+down arrow

There are also numerous variations on each of those techniques.

Also: Once you select a range you can then give it a name & be able to
select it again by opening the Name Box list & selecting the range by name.

Despite all this, I am 99% certain that 'selecting a range' is not what is
triggering that particular error message. I'm convinced that you're having
the problem because the 'Copy to:' range you're selecting does not match
[cells wide by cells tall] what's required for the data content being copied
to it by the filtering.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[kevs14]

thanks Bob, sorry for delay: Mactopia, email notfication not working. I'm going to archive this and get back..