Microsoft Excel Cannot Shift Nonblank Cells Of The Worksheet
Posted on by admin
Dell OME also doesn't show any OS information. When I check the iDrac for the R630, there is no operating system listed under System Summary and the system host name is listed as 'MINWINPC'. Hi, So, we have DellEMC Customized Image ESXi 6.5 Update 1 A10 (based on ESXi VMKernel Release Build 7967591) installed onto the dual SD cards for a Dell PowerEdge R630.
- Microsoft Excel Cannot Shift Nonblank Cells Of The Worksheet Answers
- Microsoft Excel Cannot Shift Nonblank Cells Off The Worksheet
- Microsoft Excel Can't Insert New Cells
'To prevent possible loss of data, Microsoft Excel cannot shift nonblank cells off the worksheet. Try to delete or clear the cells to the right and below your data. Then select cell 1A and save your workbook to reset the last cell used. 'to prevent possible loss of data, excel cannot shift nonblank cells off the worksheet. Select another location in which to insert new cells or delete data from the end of your worksheet.' I am using 2010.
I have a macro running that creates a row, runs calculations, makes references (values, not links), then deletes the row. It works fine for several runs, but then I get this 1004 error. 'Run-time error '1004': To prevent posssible loss of data, Microsoft Office Excel cannot shift nonblank cells off the worksheet. Try to locate the last nonblank cell by pressing CTRL+END, and delete or clear all in cells between the last cell and the end of your data. Then select cell A1 and save your workbook to reset the last cell used.' If I save the file and exit out, it works fine for a few more runs. Its a macro that will only be ran 1-2x per week, but still I want to make sure this wont pose an issue several months down the road when someone is performing data entry. I won't post the entire code, because it was recorded, and incredibly long.but if you feel that would help I can post it up. FWIW, this is the actual piece of code giving the error: Selection.Insert Shift:=xlToRight Subscribe for Weekly Excel Tips and TricksHelpful tutorials delivered to your email!Similar Topics
Hi all, I've had a long search through your pages to see if this question has been answered before but having browsed through about 50 pages worth of threads I couldn't see anything, but if I am repeating prior information I do apologise. I've written a macro that is relatively simple. It just takes some information in one format, rearranges it, adds some formatting and performs some calculations. Nothing incredibly fancy but it works fine on my computer. Now, I need to share this macro with some other people, so basically I've just sent that excel file on to the people that need to use it. Should be fine and in most cases it is, however there is one user who although they can open the file, can't seem to get the macro to run properly. It seems to get a small way through the macro but then stop with no error messages or any sign that it hasn't completed properly. I have checked Macro Security level and that is the same as mine, Tools - Add-Ins is the same, In Visual Basic, Tools - References is the same as mine. It is the same Operating system and the same version of Excel. I have even signed into this person's computer as myself (it's a big company network thing) and tried to run the macro and it works fine, so there is nothing wrong with the hardware. I've googled and searched and tried everything I can think of but I'm no closer to solving this problem, so if anyone has read through this wall of text and can come up with a possible solution, that would be greatly appreciated to save me from tearing ALL my hair out! Thanks very much for your time. I have workbook in which I want to save a specific worksheet to a new file with only the values saved - all data in this worksheet are references to cells on another worksheet, which is using VLOOKUP to pull data from a database. Found the following code and it gets me close, but it copies the cell references, not the values. It also allows me to specify the file name from a cell reference. I want the new workbook file to simply be saved, not opened, and a message box to display stating where the file was saved (will always be in the same location on the LAN). What modifications do I make to this to get this to work per above requirements? Sub CopyMe() Dim SaveMeAs As String SaveMeAs = Sheets('Sheet1').Range('B2').Text Sheets('Sheet3').Copy ActiveWorkbook.SaveAs Filename:='C:My Documents' & SaveMeAs End Sub
Hi, I'd like help in creating a macro that deletes an entire row that has emtpy cells in col B, C & D in the same row. So for example if I have empty cells in b3,c3 & d3 I'd like the row deleted. I've used the code below for just column B but I need to include column C & D as well. I tried putting Columns('B:D') but it deletes everything. Code: I'm sure it's something simple.. like me ! Any help much appreciated Wrightie I have an excel form, I want my users to be able to open the form, fill in appropriate data, then click the save button. When the button is clicked, the data in the form should be saved to a new file and then the original form should be reset (clear all values) for a new set of data entry. When the data is saved, I want the new excel sheet to be saved to a location specified within the macro so the user doesn't have any options and I want the title of the new file to be the value of one of the cells. I'm new to macros and would appreciate any help this board can offer. Thanks.. is it possible to put two formulasinto one cell? On the attached timesheet there are columns IN, OUT, IN,OUT The lunch time is worked out by deducting 'C8 from C9' and this works fine when all 'IN, OUT' cells are filled in but for some reason when just the first two cells are filled in it gives a -12 hour answer. looking at cell 'C11 ' in the attached worksheet, I currently have the formula =IF(C13=0,','SUM(C9-C8)) The reason for this is to make it look tidy by having blank cells until a calculation is needed, My problem is, If someone just comes in for the morning one day for instance 8am to 12 noon then they won't have lunch, but C11 will show -12:00 Can I put another formula into C11 to tell it not to deduct C8 from C9 until a time is put into C9. The following is the sort of thing (many variations) I have been trying but because I'm hopeless at formulas I'm not having any success Thanks =IF(C13=0,','SUM(C9-C8))OR,IF(C9,ISBLANK,',SUM(C9-C8) In Excel there is a difference between cells that are blank (= ') and that are empty. You can make blank cells empty by selecting them and pressing the delete key, but I need a fast way to do this on all blank cells in a large worksheet. The only way I have found is with a VBA macro that loops through every cell, tests for '.Value = ' ' and then uses the '.Clear()' function, but doing this on 30 columns x 10000 rows is far too slow. Any solutions? (The reason I need to do this is for importing into Access, the database treats empty cells as NULL which is what I want. Blank (but not empty) cells screw the import process up.) I get the error message 'reference is not valid' each time I open my spreadsheet. I get this message three times, and once I am done clicking ok on all three of them, my spreadsheet works just fine. The problem is, I have to send it to a bunch people. I havea sheet with raw dataa sheet with pivot tablesa sheet with a dashboardand a simple macro I don't have any #REF cells either. Someone has any idea of what could be the problem? thanks NA
Hi all, I've been getting this error on occasion recently. - It's a shared document - There is usually 10-12 people using the file at the same time I've read on the Microsoft Help site that the issue is that somebody is accessing the file while another is trying to open it. Is there a way, perhaps through VBA, that I can not allow a user to save while somebody else is opening the document? Other suggestions on how to avoid this error? Thanks! I often use if statements to return empty cells, for example: =IF(a1=0,'ERROR',') The trailing ' returns an empty cell. The problem is, it is not TRULY empty. If I fill that formula down, I cannot, for example, jump from one 'ERROR' cell to the next by hitting Ctrl arrow-down because it seems to think that these empty cells have contents. Is there a way to designate, in such a formula as above, to return a TRULY empty cell? Thanks I have a VBMacro Excel file loaded on a Server that numerous people access. A Macro in this file creates a Copy of a specific Sheet within the Active Workbook and I want to Save it to the individual's Desktop. How do I find out what the current User's desktop folder path is each time the Marco is run by a different User? Example User's path: 'C:Documents and SettingsjfarcDesktop' Where 'jfarc' is the name of the current User which, will of course change with every different User that runs the Macro. Also, is there a way to pull out of Excel what is the current User's 'Options General Default File Location' entry? Which may differ from the above directory. I am familiar with and use the following coding for Opening/Saving files to the current directory of the opened workbook, but it only gives the path of the existing Excel workbook and not the current User's Directory Path: Dim wbThis As Workbook Set wbThis = ThisWorkbook ChDir wbThis.Path Is there an on error exit sub command. I would like my Macro to just stop running if there is an error instead of an error message popping up. Thanks in advance
Posted this on the Ozgrid forums, but haven't gotten any help yet, so I thought I'd try here too! I've been having a strange problem lately. I have a fairly lengthy macro that works perfectly most of the time. Occasionally it will run as expected but as soon as the macro ends, excel becomes unresponsive to mouse-clicks. When I click anywhere (trying to select a cell, or an excel menu item..clicking anywhere in excel) I'll get the a 'ding' system sound and nothing will happen. BUT, if I use the keyboard arrow keys, I can see that the active cell selection moves accordingly. Then it gets really strange - when I have a cell highlighted and press any key to input text, it gets duplicated. So if I press 's' it will input 'ss' into the cell, and then when I press enter it will auto-move to the next cell down, but nothing ends up getting saved into the previous cell. I don't understand what's going on at all. I can't think of anything in my macro that would have these kinds of effects. I've made sure that screenupdating is turned back on at the end of every procedure. If I go into the VBE, I can manually run procedures and they all work fine. The only way I've been able to get back to normal is by force closing excel altogether and re-opening. Any ideas? ______________________ Still having trouble, and the same thing is happening with this workbook on two different computers, so I don't think it's a hardware, or OS specific issue. When it gets locked up like this, I can still do anything in the VBE (edits cells, run macros, etc.) with no problems. If I'm in the excel window, I can click alt on the keyboard and the shortcut keys for the menu come up, but I can't go deeper than that by clicking the letter shortcuts, they do nothing. If I use the delete key to delete the contents of a cell, then it gets deleted. But if I type anything else (numbers, letters, or symbols) then it types 2 instances of the key every time, yet when I hit enter, nothing changes in the cell. I also can't really bring focus to the excel window if something else (ie. the VBE) is on top of it, clicking into the excel window just gives the little system 'ding' sound and nothing happens. If I hit the save button in the VBE (since I can't click anything in the excel window), then it seems to snap out of it and go back to normal. I tried searching for anything simmilar to this and can't find anything.. Any help would be much appreciated. This is driving me nuts!
I have a large spreadsheet in Excel 2007. I am converting it to a .csv file to import in to another program. I need to delete all commas from all data. When I try to replace all commas (with nothing or with another character), I get the error message 'The formula you typed contains an error.' I have tried various formats (text, general, etc.) and various file types (.xls,.xlsx, .csv) and still get the same error. I'm having a problem in a workbook with several ActiveX command buttons. I had been using the form control buttons to run macros, but the boss wanted each button to have it's own, different color. So I removed the form control buttons and created new ActiveX command buttons. I got into the button properties and set the background colors. I added the _Click code to run the macros when the user clicked the buttons. All of the buttons were working fine. Then I saved and closed the workbook and went to lunch. Now when I open the workbook, the buttons don't work! When I click them nothing happens. They appear frozen. They don't even seem to click. No error message. Nothing. If I right-click the button in Design Mode and select Properties, I get sheet properties not the button properties. I can't seem to locate the command button properties any longer. I still see the button name 'cmdButtonGetInfo' and '=EMBED('Forms.CommandButton.1',') in the name box and formula bar. The odd thing is if I create a new button it works fine until I save and close the file. When I reopen the file none of the buttons work. It's like the buttons are being disabled when I close or open the file. Any suggestions? I am looking for the code to select the visible data after applying a data filter. Actually I know how to select the data after applying the data filter but the issue is I am not able to exclude the header row and give the target range as used (non-blank) rows only!! I am using below code to Select the Visible rows in the target range: Code: Problems in this code a 1) after applying the filter, while selecting the data it is selecting all the rows in given range till last row on the workbook. I need this to select the the data only till the last used row in the given range. 2) It is not possible to provide the address of the first row after we apply the filter since the first row address may change depending on the values in the table. E.g. 1st time when I am running the macro the first row in the visible filtered data is starting at Cell address A4 and next time when I will run the macro it may be A6 3) The Code is also selecting the 1st row which is a header row. How can we exclude it from selection. Some one please revert with the solution. Thanks in advance.
I have a co-worker's file that he is having trouble with. He is using Excel 2000 SP3. When copying a cell with a formula in it of '=D6+C6' and pasting it into the next cell down, it will display the same value in the cell as the calculated value from above, but has the correct formula displayed in the formula bar of '=D7+C7'. Example: A1: 50 A2: 10 B1: 60 B2: 20 A3: Formula: =A1+A2 Displays: 60 Right click A3, Copy, right click B3, paste A3 displays 60 When I click save, it will change the display value to 80. I am trying this on his workstation and mine. Mine has Office 2010, so I think there might be an issue with the file itself. Also, not just copy and paste. I can also just click the top cell after filling in the formula and then drag the bottom right of the cell downward and it will do the same of filling in the correct formula, but have the incorrect value. I know that I could get him to just click save each time before really looking at the results, but that is just a band aid to the problem. Any ideas how to fix this? I have an Excel workbook that was created by a former coworker. It includes a macro that, among other things, displays a message box about the 2008 file. The macro runs as soon as the file is opened. I'd like to access that macro to correct the date to 2011 and see what else, if anything, it's doing for me (it doesn't appear to do much). I can find references to creating macros to hide and unhide rows/columns and I found ways to delete all macros in a workbook, but I cannot find anything about unhiding a macro without knowing its name. Does anyone know of a way to unhide this macro? Thanks!
I have created a simple macro in this excel spreadsheet. Everything works fine until I sent the attachment to my boss. It does not work and keep showing s pop up error msg. What could be wrong?
Hi everyone, this is my first post here. I consider myself a more advanced Excel user and can navigate my way around fairly well. I've written a spreadsheet and am wondering if there is a way to permit data entry in a cell, contingent upon another cell being populated. In simple terms, you can't enter data in cell X, until cell 'Y' has been populated with something. Can anyone help with this, because I can't figure it out..
Hi all! Can anyone tell me the shortcut for Delete, Shift cells up? I have checked through Google but could not find the shortcut. Thanks!
Hi All, I have the below code which deletes all items from a listbox and my excel sheet which is the source for populating that listbox. I am using a option button style for my listbox and the selection style as single .i.e. you can select only one item at one go in the lisbox. I want my macro to delete the selected item from my worksheet .i.e. it's entire row so that it doesn't reflect in my lisbox any more. Below is my code : Code: Thanks a lot for your help in advance.
I've tried pressing shift+enter but I can't get it! I have attached a schedule for shift allowance calculation. The conditions for payment are as follows: 3 & 4 shifts = 15% Basic 2 shifts = 10% Basic If on leave for 21 working days or more, no shift allowance If on leave for less than 21 working days and on 3 or 4 shifts, shift allowance prorated as (Basic*15%)/21*(diff. btw 21 and no of days leave observed) If on leave for less than 21 working days and on 2 shifts, shift allowance prorated as (Basic*10%)/21*(diff. btw 21 and no of days leave observed) The formular I used in the attached worked but the only problem is that I had to sort by no. of shifts and adjust the formular first. I need A formular that does not require sorting. I tried to combine IF, AND, OR formulae but I got error. Please assist me sort this out. Thanks a bunch.
Hello, I am new to using Excel and I am not very computer literate. I am looking for some help with a spreadsheet I am making. Here is the problem: I have a column of 14 numbers in cells A1 to A14. If all of these values are less than 2%, I would like a 'Pass' to display in cell B1. If any of these values are greater than 2% I would like a 'Fail' to display in cell B1. I have got a formula that works for a single cell but I can't figure out how to get it to apply to more than one cell. For example, I have entered =IF(A1<2,'Pass','Fail') in cell B1. Now if the value I enter in A1 is less than 2, it shows a 'Pass', and if the value in A1 is greater than 2 it shows a 'Fail' in cell B1. How can I get this to apply to all of the cells from A1 to A14? If somebody could help me out that would be great. Thanks, Randy
Hello I'm quite experienced Excel user. I've never come across this problem but tinkering in every conceivable way within Excel settings and the solution has eluded me. I have added a worksheet created elsewhere (it is a form I need printing, with the data coming from 2 sheets I have created from scratch) which has pre formatted cells for Date and Client Name etc. When I try to reference the cell in this added sheet from my 2 sheets, instead of the result, it always displays the formula, not the result. I have tried doing it from one of my sheets to reference to this new, and that displays the result and not formula. I can't imagine why it's doing this and I've never seen it happen before. Formatting cells, giving cells names rather than the usually adequate of reference to the Cell Number doesn't change things. I wonder if I've picked up some legacy protection from the original form but can't see anywhere in the tools etc that's obvious. There's about 50+ cells that need referencing and I got to get this done for work. Please help me. This is my first need to post on a Excel Forum as I've always found help or answers from other peeps or internet but this one is making me scratch my head big time. |
Active8 years, 9 months ago
I have an excel with 8 columns. I'm trying to add a new column at the beginning of the worksheet. The existing columns should shift.
Below is the code which I tried:
But I'm getting the following error:
To prevent possible loss of data, Excel cannot shift nonblank cells off of the worksheet. Select another location in which to insert new cells, or delete data from the end of your worksheet. If you do not have data in cells that can be shifted off of the worksheet, you can reset which cells Excel considers nonblank. To do this, press CTRL+End to locate the last nonblank cell on the worksheet. Delete this cell and all cells between it and the last row and column of your data then save.
VijayVijay
2 Answers
Microsoft Excel Cannot Shift Nonblank Cells Of The Worksheet Answers
Vinay B RVinay B R6,6482 gold badges22 silver badges42 bronze badges
Microsoft Excel Cannot Shift Nonblank Cells Off The Worksheet
VijayVijay