Hi, If you have a formula in a cell, it is never considered blank, even if the formula returns an apparent blank.
You will often get this with 'IF' formulas: eg IF(A20=5,"Bing","") If A20 does not equal 5, then the formula returns a "". But XL sees this as a zero-length string, not a blank cell. You will need different code in your Access code; something that tests for zero-length strings. Instead of "is not null" perhaps "len(cell ref)=0" I'm not too familiar with Access, so you'll hav to figure that part out. Regards - Dvae. > Date: Fri, 19 Mar 2010 11:31:50 -0700 > Subject: $$Excel-Macros$$ replace code changes blanks cells > From: mwillia...@socal.rr.com > To: excel-macros@googlegroups.com > > I have this code that replaces a "-" in a column. > It does just that, but when I link that workbook to Access and use a > "is not null" filter in a query the ones that are blank come up in the > query. > Thus my code is doing something to each cell to make Access think the > cell is not blank (whent it appears to be) > > Any ideas??? > > before code is run > > Cell A1...A100 = "test-me" > cell A101...A65000 = blank > > Access query brings up one row, A1 > > > after code is run > > Cell A1...A100 = "testme" > cell A101...A65000 = blank > > Access query brings up 100 rows (that also appear blank) > > > > Code: > > Dim er As Double > > er = Range("o65000").End(xlUp).Row > > Range("o2").Select > > Do Until ActiveCell.Row - 1 = er > > ActiveCell.Value = Replace(ActiveCell.Value, "-", "") > ActiveCell.Offset(1, 0).Select > > Loop > > -- > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our Facebook Group @ > http://www.facebook.com/group.php?gid=287779555678 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 6,800 subscribers worldwide and receive many nice notes about > the learning and support from the group.Let friends and co-workers know they > can subscribe to group at > http://groups.google.com/group/excel-macros/subscribe > > To unsubscribe from this group, send email to > excel-macros+unsubscribegooglegroups.com or reply to this email with the > words "REMOVE ME" as the subject. _________________________________________________________________ Looking for a new home? With all the latest places, searching has never been easier. http://clk.atdmt.com/NMN/go/157631292/direct/01/ -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words "REMOVE ME" as the subject.