Home > Excel Vba > Excel Usedrange Not Working

Excel Usedrange Not Working

Contents

The solution that I found about a year ago was to use the Find function with the wild character "*" (see code below). Help needed! Since .UsedRange is so handy to use in VBA, I usually workaround this by crafting a sub that checks for the last occupied column and row in the sheet and Deletes Thank you for the feedback! check over here

Try clearing/deleting all the cells after your last row. Share Share this post on Digg Del.icio.us Technorati Twitter Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac The more we learn, and the better we Wrong way on a bike lane? @asyncio.coroutine vs async def Has a movie ever referred to a later movie? Thanks (0) By David Ringstrom Jun 26th 2015 01:11 Thanks for sharing your experience, Willem.

Activesheet.usedrange Reset

Reply Debra Dalgleish says: March 1, 2012 at 2:45 pm @Jason, thanks, I've never used that Ctrl+Shift+Spacebar shortcut! It is actually a brilliant idea, search for "*" but set the order to xlPrevious. Public Function GetActualUsedRange(ws As excel.Worksheet) As excel.Range Set GetActualUsedRange = ws.Range("A1″).Resize(ws.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _ ws.Cells.Find(What:="*", SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Column) End Function Reply Jim Roycroft says: August 18, 2015 at Dom Reply Debra Dalgleish says: March 1, 2012 at 3:39 pm @Domski, in Excel 2010, if I format cell E10 with white font, then change it back to No Fill, that

Want to post a small screen shot? Your cursor will now return to cell TX5000, even though you erased it Please Login or Register to read the full article To access all of the content on our site, Currently there are two rows of data; A2514 - I2514 A2516 - I2516 Any ideas? Excel Vba Usedrange Last Row Share Share this post on Digg Del.icio.us Technorati Twitter Rick's "mini" blog...

Reply TheoDeed says: September 2, 2015 at 8:41 pm I suppose you need to activate the sheet like this: Worksheets(1).Activate Instead of (1) you may use ("Sheet1") or another number if Usedrange.rows.count Vba So my question is, what (if anything) can be done to make the Find function look in A1 too? (I realise I could just test if cell A1 has something in Your code works wonders if there is an actual value in the range. Thanks for pointing that out.

http://www.excelfox.com/forum/f22/ . Excel Vba Set Usedrange Thanks (0) By wesejohnson Jun 26th 2015 01:12 This doesn't occur frequently enough for me to remember the steps, so I came here and found the End-Home keystrokes to travel Reply With Quote Jan 16th, 2012,05:09 AM #5 westconn1 View Profile View Forum Posts PowerPoster Join Date Dec 2004 Posts 22,002 Re: [VBA Excel] Actual used range - Find function not Search Enter your keywords Login Register Technology Excel Resetting the Last Cell in an Excel Worksheet xfgiro/istock Apr 22nd 2013 27 It's frustrating when Excel acts as if the active area

  • Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next dim all variables as required
  • I decided to look into this function in more depth and found that some people will specify the second argument (an optional argument) as follows: Code: First_Row = XL_Ws.Cells.Find("*", After:=[A1], SearchDirection:=xlNext,
  • Thanks (0) By David Ringstrom Jun 26th 2015 01:11 Thanks for your feedback, Bob, and for adding my article to your curated list.
  • All contents Copyright 1998-2016 by MrExcel Consulting.
  • LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear Mode Switch to Hybrid Mode

Usedrange.rows.count Vba

Used to have the Select Visible Cells button on my Excel 2003 toolbar, so I'll have to add it to the Ribbon in Excel 2010 too. Okay, you should have two cells selected… change their Fill Color to, say White, and then change it Immediately back to No Fill. Activesheet.usedrange Reset How to draw a line same to documentation by programal method? Activesheet.usedrange Not Working We have to execute it byRows and byColumns to get the last Row and Column.

Select Visible Rows is hidden on "other commands" Reply Debra Dalgleish says: March 1, 2012 at 2:47 pm Thanks Alex! check my blog Changing 'theorem' to 'Theorem' while using \cleveref{} How to plot a simple circle in LaTeX Blueprint a sestina Teenage daughter refusing to go to school Bochner's formula on surfaces using moving Thanks (0) By David Ringstrom Jun 26th 2015 01:11 Once in a while I find that it won't work on a particular worksheet. Can someone help me fix this? Used Range Excel Vba

Reply nagajothi says: March 5, 2012 at 2:21 pm very useful information…. What power do I have as a driver if my interstate route is blocked by a protest? Thanks (0) By David Ringstrom Jun 26th 2015 01:12 Excellent! this content I have one more twist.

Also, running Office 2013, don't know if that's the reason why I had the trouble. Used Range Oven If you need to identify formula cells that might be displaying the empty string, then change the xlValues argument to xlFormulas. I also read the related posts you guys linked which includes this Private Sub Workbook_BeforeSave _ (ByVal SaveAsUI As Boolean, Cancel As Boolean) For Each Sh In ThisWorkbook.Worksheets x = Sh.UsedRange.Rows.Count

The find method wraps around the sheet to find the last used cell.

Try MrExcel HTML Maker How To Use MrExcel HTML Maker: http://www.mrexcel.com/forum/about-b...ml#post2545970 Reply With Quote Mar 26th, 2013,11:04 PM #4 lordterrin Board Regular Join Date Mar 2012 Posts 155 Re: ActiveSheet.UsedRange.Rows.Count is is it because you have data in rows underneath where you wish to stop? excel vba excel-vba share|improve this question edited Jul 29 '13 at 15:52 asked Jul 29 '13 at 0:12 user2600411 1918 Have you tried resetting the used range see here Excel Vba Usedrange Vs Currentregion If the above doesn't work, try copying and pasting your "real" area of your worksheet to a new, blank worksheet, which should do the trick as well.

With Rick's example, it isn't selected. Reply Keith says: May 6, 2014 at 9:27 am Very useful information. Office 2013 isn't the issue, and as you noted, the correct syntax to type in the Immediate window is: ActiveWindow.UsedRange and after you type that press Enter. http://tubemuse.com/excel-vba/excel-vba-autofit-not-working.html Of course, you realise that in order for you to know that I seem to be on every Excel board on the planet then you must also ...

After a few attempts this worked: Nav to the last active cell using End-Home > highlight the blank rows back to your desired data > Clear all > then delete the Secondly, this code has far more versatility if you use it as a function so that it can be used on any worksheet instead of just on the active worksheet. In other words, the code only identifies the "last whatever" for visibly displayed values (no matter if they are constants or the result of formulas)… that means there could be cells It's driving me crazy!

Thanks (0) By Anita Jun 26th 2015 01:11 Hi David,thank you so much for the help! If you don't like Google AdSense in the posts, register or log in above. Used this today, from Access with automation. Bochner's formula on surfaces using moving coframes Why is the 'You talking to me' speech from the movie 'Taxi Driver' so famous?

Using transistor as switch, why is load always on the collector Which security measures make sense for a static web site? You seem to be on every Excel board in the planet. Will I get a visa again?