I will say, however, that If you really wish to use UsedRange, your code above is still wrong to get the rows. 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 John Hughes, 1986 Reply With Quote « Previous Thread | Next Thread » Like this thread? Thanks to Earlien and Westconn1 Reply With Quote Jan 30th, 2012,07:55 PM #8 Earlien View Profile View Forum Posts Thread Starter Member Join Date Dec 2011 Location Brisbane, Australia Posts 43 Source
In one of the steps you say: "Move your cursor up one cell and then press the Delete key to clear cell TX5000." This is only true if the Excel editing Life moves pretty fast. I would suggest using Range("A" & Rows.Count).End(xlup) that way your code is realiable for multiple versions of excel. –Reafidy Sep 15 '11 at 10:05 @Readify: indeed! But there is also a simple method to clear all unwanted cells from that point back to your "desired" last cell (which I never remember).
My UsedRows does NOT start on row 1, it starts on row 5, which means this all makes sense now. IfMsgBox No return Msgbox, This will take some time. Why are Halloween and Christmas the same? Will I get a visa again?
- Cheers, Ger Check out our new reputation system.
- I am ...
- share|improve this answer edited Jul 12 '12 at 18:38 davidlandy 433 answered Sep 14 '11 at 21:33 Reafidy 4,79521954 +1 Nice, I learned something. (see?) –Jean-Francois Corbett Sep 15
- Reply Gregory says: March 3, 2012 at 5:55 pm My apologies.
- Thanks (0) By David Ringstrom Jun 26th 2015 01:12 I'm glad you found this helpful!
Up till now I had just used the code above which I ripped from somewhere on the internet (like this site) without understanding the components of the Find function. So try running this sub procedure: Sub ResetUsedRng() Application.ActiveSheet.UsedRange End Sub Failing that you may well have some formatting hanging round. Mimsy were the Borogoves - why is "mimsy" an adjective? Excel Vba Usedrange Last Row Advanced Search Forum TIP, TRICKS & CODE Reset the used range Excel Training / Excel Dashboards Reports IMPORTANT INFORMATION The OzGrid Free Excel & VBA Help Forum will beupgrading to the
Thank you. Usedrange.rows.count Vba more stack exchange communities company blog Stack Exchange Inbox Reputation and Badges sign up log in tour help Tour Start here for a quick overview of the site Help Center Detailed Thank you! Join them; it only takes a minute: Sign up Getting the actual usedrange up vote 14 down vote favorite 11 I have a Excel worksheet that has a button.
Instead find the last row and use that for pasting. Excel Vba Set Usedrange Otherwise need to use the SpecialCells solution. Code: 'Creates column X which will be used to sort Subscription from Perpetual Range("X6").Select ActiveCell.FormulaR1C1 = _ "=IF(ISERROR(FIND(""OLV"",RC[-9],1)),""Subscription"",""Perpetual"")" Range("X6").Select 'Select cell X6, then autofill the contents of U6 down to whatever Used this today, from Access with automation.
To start viewing messages, select the forum that you want to visit from the selection below. Very quickly… go to a blank sheet and select cell A1, then press and hold the CTRL key while pressing some other key (say, for example, J10). Activesheet.usedrange Reset Solve using Cauchy Schwarz Inequality "The Blessed One", is it bad translation? Activesheet.usedrange Not Working Looking at the sheet, you might say "nothing".
Thanks (0) By David Ringstrom Jun 26th 2015 01:11 Thanks for your feedback, Bob, and for adding my article to your curated list. UsedRange returns incorrect result in Excel Started by stressbaby , Nov 16 2012 07:51 PM Please log in to reply 5 replies to this topic stressbaby Members 153 posts Last active: 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 The code works well only once. Used Range Excel Vba
Reply Gregory says: March 3, 2012 at 2:43 pm I'm not sure why you wouldn't use something simple to select the used range on a worksheet, like: ActiveSheet.UsedRange.Select or if you I have one more twist. Powered by vBulletin Version 4.2.3 Copyright © 2016 vBulletin Solutions, Inc. Thanks (0) By BW Jun 26th 2015 01:11 If you look at the picture, Figure 2, with the text in the bubble tagged "4", it says Active.Sheet.UsedRange.
If you need to identify the absolute "last whatever" containing data or formulas (no matter what that formula is displaying), then change the xlValues assignment to xlFormulas for the all of Used Range Oven Suddenly your scroll bars move you into uncharted areas, such as column TX or row 5,000. All times are GMT -5.
How does Gandalf end up on the roof of Isengard? Reply Rick Rothstein (MVP - Excel) says: March 1, 2012 at 4:10 pm As Debra has mentioned, UsedRange does not always return the true used range on the worksheet. Dealing With Dragonslayers Is there a way to block an elected President from entering office? "president-elect" grammatically correct? Excel Vba Usedrange Vs Currentregion Select the Used Range To select all the cells in the used range on a worksheet, you can use the following shortcut sequence: Press Ctrl + Home, to select cell A1
It sounds like your workbook might have some sort of minor underlying data corruption. Most web sites I have visited suggest a workaround, and generally they go along the lines of a function like this:Private Sub Test2() Dim XL_Ws As Excel.Worksheet Dim First_Row As Long, Have you tried using the End property to identify the end of the dataset? #2 - Posted 16 November 2012 - 08:42 PM Helpful LinksRecommended: AutoHotkey BoardsTutorial for NewbiesQuick Search for Insert the instruction just before ActiveSheet.UsedRange.Copy Good luck.
The time now is 11:44 AM. All of it in general? share|improve this answer edited Aug 26 '15 at 4:58 EI Captain v2.0 10.3k33155 answered Aug 25 '15 at 20:21 Harold 1 add a comment| Your Answer draft saved draft discarded Like a section of data, with some blank rows, and another section? –Scott Holtzman Aug 9 '12 at 15:31 @ScottHoltzman which part of the logic?
I use Ctrl+Spacebar and Shift+Spacebar so this must be part of the same family of shortcuts. Thanks in advance!