VBA autofilter on computed table column does not work?

**brucemc777** · 01-08-2016, 07:53 PM

I find that if I use VBA to run an autofilter on a table column of values it works fine, but if I use the same VBA to run an autofilter on the column adjacent to it that has computed values (a formula, no matter how simple, instead of a value, even though the formula displays a numeric value) the filter collapses every single record (row). If I engage the autofilter manually from the spreadsheet on the computed column it works fine - only bad when I run it from VBA.

I am about to put a band-aid on it by running a copy - pastespecial - values on that table's column and running the autofilter on that column, but I think there must be a better way!

Thoughts?

**FDibbins** · 01-08-2016, 08:17 PM

My VBA skills are weak at best, but...
if it works on values
if it works manually on formulas
if it does not work with code...

Then maybe your code is at fault?

**FDibbins** · 01-08-2016, 08:24 PM

I just created a 2=column range, 1 column values, the other a formula. I then recorded a macro to sort on the values columns - then chaged the macro range to sort based on the formula column. Both sothed without a problem....

Please Login or Register  to view this content.

**skywriter** · 01-08-2016, 08:26 PM

Post your workbook.

ViewPic

**brucemc777** · 01-08-2016, 08:39 PM

You tell me, here's what I got - olow0s8 is the name of the table.

Please Login or Register  to view this content.

The line that is not working is

Please Login or Register  to view this content.

If i change the field to 10, obviously immediately adjacent and prior to the errant run column, it filters it perfect.

The only difference between the two is that column 10 has values and column 11 is a SUM of three values.

Just saw your new post come in, about to read-

@skywriter - Will try, need to redact the info...

**brucemc777** · 01-08-2016, 09:13 PM

If I told you I had a tooth pulled yesterday that took an hour and a half to rip out and should stay away from my computer when on meds, will you cut me a little slack?

I am going to shut up now and not post for a couple of days. I found the error. Please pardon me and any trouble I caused...

@FDibbins: In addition to the other one from today, would you be so kind as to make this one disappear?

**FDibbins** · 01-08-2016, 09:27 PM

You are not causing any trouble or problems at all, so please dont feel you are

I know all about having problems pulling teeth, I hope you feel better soon. 1 of the biggest problems with written comms like this, is that it can be hard to tell the "mood" of what is said, just be reading what was said. I doubt anyone was busting on you (I know I wasnt)

Often what is glaringly obvious to you, we cannot see at all - and vise versa. Get some rest and get back to us when you can. If you need to get back to this thread in a day or so, "subscribe" to it (unless you already have?), so you can find it easier

**skywriter** · 01-08-2016, 09:42 PM

will you cut me a little slack?

No way, let the tightening begin!!!

Been there done that. I had two impacted wisdom molars pulled back in the early 80's. While I was waiting for the drugs to kick in I heard the girl in the next room crying hysterically while the oral surgeon pulled her teeth. When he came back in to check on me he saw the look on my face and said, "relax she didn't feel a thing she's just being hysterical." My response was, "I only care whether I'll feel it or not, if I don't feel it I don't care." I didn't feel a thing and the after drugs were pretty good too.

Speedy recovery,
Bruce(skywriter)

**brucemc777** · 01-08-2016, 10:58 PM

Thanks Gentlemen-

Dang. I just violated my "not post for a couple days" promise...

**skywriter** · 01-08-2016, 11:09 PM

Originally Posted by brucemc777

Thanks Gentlemen-

Dang. I just violated my "not post for a couple days" promise...

Must be the meds.

**gbeats101** · 01-08-2016, 11:09 PM

Hi, not sure if you managed to get this problem solved, i didnt really plan on looking through all the arguing going on but, there are certain datatypes require you use xlbetween and use < and > together to single out a value and not xlEquals, its something i came across a while back and at the time seemed strange.... but then again most errors seem strange in VBA.......give it a try and let me know.

Thanks
Paul S.

**brucemc777** · 01-08-2016, 11:13 PM

It was a very dumb (hopefully I can write it off to "med influenced") mistake, the second that I made and posted over today, but Thank-you nonetheless!

VBA autofilter on computed table column does not work?

LinkBack

Thread Tools

Rate This Thread

Display

VBA autofilter on computed table column does not work?

Re: VBA autofilter on computed table column does not work?

Re: VBA autofilter on computed table column does not work?

Re: VBA autofilter on computed table column does not work?

Re: VBA autofilter on computed table column does not work?

Re: VBA autofilter on computed table column does not work?

Re: VBA autofilter on computed table column does not work?

Re: VBA autofilter on computed table column does not work?

Re: VBA autofilter on computed table column does not work?

Re: VBA autofilter on computed table column does not work?

Re: VBA autofilter on computed table column does not work?

Re: VBA autofilter on computed table column does not work?

Thread Information

Users Browsing this Thread

Similar Threads

AutoFilter on Protected Table Does Not Work with VBA

[SOLVED] How to present a computed column in a pivot chart?

Adding a Computed column at end for a growing list using Fn-No macros

Adding a Computed column at end for a growing list using Fn-No macros

[SOLVED] how do i get my data table column inputs to work?

How do I sort a column of computed variables in EXCEL?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions