Excel 2010 :: Opens To Blank Row After Last Row Of Data?

Mar 31, 2014

When I had Excel 2010 and now with Excel 2013, whenever I open a workbook, it opens and displays to the row AFTER the last row of data. (So, if my last row of data is 38205, it will open starting at row 38206...hence I see no data until I hit Ctrl+Home). I have not found anything about this on the internet and I cannot find a setting. I need Excel to open to A1 so I don't freak out every time I open a workbook and see no data.

View 2 Replies


ADVERTISEMENT

Excel 2010 :: Opens New Instance For Each Fie

Dec 12, 2012

In the last couple of weeks my copy of Excel 2010 has started acting strange. Now when I double click an xls or xlsx file in Windows Explorer, it tries to open a new instance of Excel, whic means that it tries to reopen Personal.xlsm and Personal.xlsb, and finds they are already open, so it gives me an error message. Previously it perfectly happily opened the document in another window in the same instance, which is presumably much more memory efficient quite apart from the file locking problem mentioned. How do I get it back to the way it was before? Is this another of MS's famous overnight downgrades?

View 2 Replies View Related

Excel Opens Existing Document Not Blank One

Mar 22, 2013

When I click the general Excel program icon that use to just open a blank excel workbook, it now always opens the same existing file (apples.xls) If I am clicking on a different existing file (pears.xls), excel opens pears.xls and apples.xls.

View 4 Replies View Related

Excel 2010 :: Transposing Data With Blank Rows In Columns

Feb 10, 2014

We utilize large data sheets that can be as large as 300K in rows and 10 to 15 columns wide. Because of how we receive the data, we are forced to manipulate things so that all matching data for a record ends up on a single row. (e.g. Record#, Document Type, Husband Name, Wife Name, Wife Maiden Name, Etc.)

Right now here's how the data is received:

a
a
a
b
b
c
c
c
c
d
d
d
e
e
e
e

Using two vba scripts, we first separate the data with row spaces between the unique data as follows:

a
a
a

b
b

c
c
c
c

Then with another script, we transpose the data as follows:

a a a
b b
c c c c
d d d
e e e e

When we transpose the data, the end result starts at the top of the page and go down eliminating the original blank rows. Not a huge issue but I would like to be able to maintain the original data format of the rows so that the data matches the original sheet line for line. The end result would give me the data as follows...

a a a
b b
c c c c
d d d
e e e e e

where the vertical gaps between the letters matches the original rows. Like I said, not a huge issue since we can rejoin the transposed data to the original data fairly easily. But it would be nice if we could end up with the above format for speed sake.

The two scripts we use, one-to insert the rows and two-to transpose, take a very long time to run with the transpose script taking the longest by far. On a 30K row sheet, it will take on our systems around 30 minutes to transpose and about 15 minutes to insert rows. Because we have several columns that need to be transposed, a 30K row sheet will take at least 2 hours to complete. A 300K row sheet, that will take 10 to 15 hours to complete.

Is there any way to speed up the scripts either by upgrading to a faster CPU and or writing the scripts to preform faster?

My preferred solution would be to write (have) a formula to preform the transposition that gives me the results as noted above since formula's run so much faster than vba. Is this possible? I have tried all kinds of formulas and can not come close and of course the straight transpose function does not give me the solution I need as noted above.

I have enclosed an excel 2010 spreadsheet with 10K rows of data in rows along with the scripts I use (nothing sensitive here). The tabs at the bottom shows you the data before I transpose, then the data after it has been transposed . To speed up the scripts, I have stripped away the all the rest of the data from the original sheet except just what I need to transpose at one time. Once that is completed, we then re-join the transposed data with the original sheet. The six digit number you see to the far left of the data is the record ID number from the original data. We use this to rejoin the transposed data with the original data so that we know everything is back where it should be. (Note: The insert rows script is run on the original data and not the data you see on the enclosed spreadsheet. That is the only way we can generate unique rows with matching ID numbers. We arrive at this by taking the original data, concatenate the record ID with the column we want to transpose and add a # between the two so that we can break things back apart after the transposition using the text to column function using the # as the separator.)

The sheet I have attached is in the 2010 Macro Enabled format...(xlsm format). We use the xlsb (binary) format for the data to reduce the file size as our normal procedure and run the macros from inside that format. Changing from the xlsx to xlsb format did seem to speed up the scripts a bit and greatly improved the file performance as a whole e.g. saving and loading.

One thing I have done to speed up the scripts is to strip all the data away that is not needed for the transposition. That did work but only a marginal amount.

We are using windows 8.0 with 4G memory and your basic processor speed...e.g. nothing fancy.....just your basic stock computer. Nothing else unusual is installed or running on the computer or at the time the scripts are running.

For those of you that process large sheets, how much of a performance upgrade will we see in processing our scripts by either upgrading memory to 8G (or more or much more) and or getting a faster processor? Or have we reached the maximum script speed already? Or is this a limit to Excel.

One other issue to note: As I stated above, on the 30K row sheets, not a super problem with about 2 hours needed to run the scripts on all the data on the sheet. But on the 300K row sheets, it can take 12 or more hours to run and there are times when things 'lock up' running the scripts on sheets this size.

View 5 Replies View Related

Excel 2010 :: Autofill Entering Data Against Blank Cells

Jan 23, 2014

In earlier versions if I used the double clicked the autofill handle it would only fill down to the next adjacent cell containing data. Now it fills down to the bottom of all the data regardless of whether there is a blank cell or not e.g

X X X Z
X X X Z
X X X Z
X X
X X X

Now this happens

X X X Z
X X X Z
X X X Z
X X ...Z
X X X Z

How can I stop this as I need to enter different data in the next section?

View 1 Replies View Related

Excel 2010 :: SUMPRODUCT Answer To Return Blank When No Data In Specific Cells

May 12, 2013

I am using Excel 2010 and I am trying to average the amount of days in a month to a daily average per person in my worksheet.

Total sales per person
A5 = 10 - This is the Grand total per person for column A
A6 =4
A7=6

Daily average per person
C5=2.6 - Average for all persons here
C6=2.0
C7=3.0

The formula I am using is:

=(SUMPRODUCT($A$6:$A$15,C6:C15))/$A5

Which gives me an answer of 2.6 in cell C5 as shown above which is what I am wanting.

Please note that my cell range for my staff goes from 6-15 for both Column A and C where the other cells are blank in both columns.

My question is, If I was to clear all the data in both Columns A6:A15 and C:6:C15, cell C5 would return to a #VALUE. How to I change the formula so that if the cells were Blank, cell C5 would also be blank until I enter data for each person again?

View 2 Replies View Related

Excel 2010 :: VLookup Result Blank If Column Index Number Is Blank

Mar 3, 2014

The below piece of code carries out a vlookup on a defined cells value and produces a result in sheet one, however if the column index number in sheet 2 (Database) is empty the result 00/01/1900 is produced.

I'm not sure how to say leave the result blank if the column index number is blank.

Excel 2010
Userform = Tab 1
Database = Tab 2

View 6 Replies View Related

Opening Saved File Also Opens Blank Workbook

May 3, 2013

When I open any saved Excel file the saved file opens along with a blank workbook titled "Book 1". How can I make it so when I open any saved Excel file it only opens the file I want and not a new workbook every time?

View 2 Replies View Related

Excel 2010 :: Average Non-Blank Or Zero Cells

Sep 10, 2012

I have a sequence of 40 columns of data, representing 10 weeks worth of events with 4 values per week

I need to average the first pair for each week, ignoring zeros and blanks, and also separately the 2nd pair of cells, across the whole 40 columns

I am trying to come up with a neater solution which takes into account the zeros, at the moment I have the following which does not, for row 34

=IF($AR34>0,ROUND(AVERAGE(D34:E34,H34:I34,L34:M34,P34:Q34,T34:U34,
X34:Y34,AB34:AC34,AF34:AG34,AJ34:AK34,AN34:AO34)*2,2),0)

I could use SUM, COUNT COUNTIF etc but its gonna get messy

Using EXCEL 2010

View 5 Replies View Related

Excel 2010 :: VLookup For First Non-blank Cell

Feb 5, 2013

I have a table in columns A-D. I am trying to perform a vlookup to return the first non-blank in columns B-D based on the lookup value in column A. Columns F-I are what I am looking for the formula to do. I think I am close with the following array formula but not quite there.

Excel 2010LMN12#N/A3Sheet1Array FormulasCellFormulaM2{=INDEX($B$2:$D$9,MATCH($F$2,$A$2:$A$9,FALSE),
MATCH(TRUE,INDEX(INDEX($B$2:$D$9,MATCH($F$2,$A$2:$A$9,FALSE),0)<>"",),FALSE))}Entered with Ctrl+Shift+Enter.
If entered correctly, Excel will surround with curly braces {}.

Note: Do not try and enter the {} manually yourself

Excel 2010ABCDEFGHIJKL1PFQPFQ212341234CRWEWS312343456
WETXDR41234CR5123461234WE712348123491234WS1034562)

What I want it to return, vlookup of first non-blank
113456WE123456DR133456143456TX153456163456173456183456191)  
What table looks like20Sheet1

View 4 Replies View Related

Excel 2010 :: Pivot Table Blank Column?

Jan 27, 2014

Why do blank columns with no information sometimes appear when I prepare pivot tables? Excel Version 2010. I hide the columns, but many times they re-appear on screen or print-outs.

View 1 Replies View Related

Excel 2010 :: Bottom To Top Counting With One Criteria (Up To Blank)

Jul 7, 2014

find the attached excel 2010 file <Count LBP or UBP.xlsx>.

My requirement is if `LBO' is available (in Column A) then the formula should count the number of `LBP' available above the `LBO' upto the blank cell. If there is no `LBP' above the `LBO' then it should plot zero in Correponding Column B Cell. If 2 `LBP's available above the `LBO' upto balnk space it should plot `2' in Correponding Column B Cell.

As like `LBO' and `LBP' the formula should work for `UBO' & `UBP'. refer the attached excel file.

View 6 Replies View Related

Excel 2010 :: Auto Summary From Non-Blank Cells

Aug 21, 2012

I have created a comparison sheet and compares my old one from last week to the new one I create for this week.
It populates cells with the differences and shows me the data from the new sheet. But since my they are so massive A5:AZ20,000 I want to create an auto summary or something.

Column A has each properties Unique Identifier And the rest is various info for the property. I would like it to return my unique ID and then the columns with new data that has showed up from the comparison some are text and some are numbers.

Also I am using Excel 2010.

View 6 Replies View Related

Excel 2010 :: Changing Default Blank Page Settings?

Apr 23, 2012

My office recently upgraded to Office 2010 and we would like (in the accounting department that I work in) to change the default number formating in a blank sheet to Number, 0 Decimals, using seperators, from the current default of general format number.

I have looked for the Book.xltx file to replace but can't see it any where.

View 5 Replies View Related

Excel 2010 :: How To Fill Blank Cells Pivot Table

Dec 19, 2012

how to fill the blank cells in a Pivot table. I am using Excel 2007. How to do it in excel 2007. Heard that there is a provision in excel 2010 version.

View 9 Replies View Related

Excel 2010 :: Inserting Blank Row In Table Without Copying Formula

Mar 15, 2013

I am trying to insert a blank row into a table (created using the table function in Excel 2010) without the copying the formulas. Every time I insert a row the the formula is copied.

View 3 Replies View Related

Excel 2010 :: Not Able To Remove Blank Cells In New List Keeps Repeating Same Item?

May 21, 2013

I have a list that I need to move to another column without spaces. I have it moving without spaces but it is only one item over and over again. I am using Excel 2010 and that may be my issue. I have attached the exact worksheet and formula.

To Buy List.xlsx‎

View 2 Replies View Related

Excel 2010 :: Insert Page Breaks Automatically In Blank Rows?

Jun 14, 2014

We have a schedule that creates an Excel file which separates each order by blank rows. I need each order to be separated by a page break, so each order prints on a separate sheet.

Each order entry is 1 or 2 rows

Using Excel 2010/2013 although the file produced is an xls file.

Columns A to J are populated

Rows 1 & 2 are the header

I have tried a macro that involves me doing a countif and counting entries in the row, and if they add to 10 it inserts a page break, but its bit long winded and only seemed to work once.

View 3 Replies View Related

Excel 2010 :: Filter To Select Records - Removing Unwanted Blank Cells

Jul 25, 2013

I have a spreadsheet which is used by users unfamiliar with Excel. They are using the filter to select records, however when this is used some records appear which have no entry in the cells of that column. Can I overcome this? There is no data in the blank cells, other than a data validation drop down.

View 1 Replies View Related

Excel 2010 :: Check If Cell Not Blank Upon Saving Or Closing And Color Code To Show Blanks

Aug 30, 2012

Looking forward VBA coding for:

If input is entered into column C (range C2:C100), then row cells for columns D, E ,F, H or I must NOT be blank upon save.
Could be that one or more of these row cells are left blank by mistake.

An error msg pops up upon attempting to save, stops the save and colors each cell yellow that needs info entered into.

Using Excel 2010.

View 9 Replies View Related

VBA That Opens Excel In New Window

May 3, 2014

I have a project were i have to make a macro and place in a workbook such that as soon as i open that excel file

1. excel workbook is opened in New Excel window
2. Ribbon is hidden for this new window.

So when user closes this excel window there is no problem with other workbook ribbon, as there are open in different window

I have tried with code stated below it hides the ribbon but new excel window is not open

Private sub Workbook_open()
Application.ExecuteExcel4Macro "Show.toolbar(""Ribbon"",False)"
End Sub

View 2 Replies View Related

Code That Copies A Range Opens Notepad Pastes The Range Opens Save Dialog And Types The File Name

Nov 6, 2009

I currently have the following code that copies a range opens notepad pastes the range opens save dialog and types the file name. The problem I have is with overwriting the existing file.

Range("A1:A202").Select
Selection.Copy
Shell "notepad.exe", vbNormalFocus
SendKeys "^V"
SendKeys "^s"
SendKeys "Total_IEDs_Hour_Of_Day_2009.xml"
SendKeys "{TAB}"
SendKeys "a"
SendKeys "{ENTER}"

Everything works fine to this point. Then it opens the do you want to overwrite dialog and I cant get it to hit yes.

View 9 Replies View Related

Conditional Macro When Excel Opens?

Sep 28, 2013

I want to run a VBA macro when Excel opens if and only if the file name contains a specific string or if and only if the worksheet name contains a specific string. The file only contains one worksheet. For example, if the file name contains the string "SalaryData" or the sheet name contains "SalaryData", then I want to run a VBA macro to delete some of the confidetial information. Idealy, this would be done as the file is opening before it is displayed.

View 1 Replies View Related

Macro That Opens Excel In New Window?

May 2, 2014

I have a project were i have to make a macro and place in a workbook such that as soon as i open that excel file

1. excel workbook is opened in New Excel window
2. Ribbon is hidden for this new window

i have tried with code stated below it hides the ribbon but new excel window is not open

Private sub Workbook_open()
Application.ExecuteExcel4Macro "Show.toolbar(""Ribbon"",False)"
End Sub

View 1 Replies View Related

Excel Opens Multiple Workbooks When Opening Any Workbook?

Jan 27, 2005

The same two workbooks open when I open any other workbook. They also open up when I open Excel by itself.

View 9 Replies View Related

Excel 2010 :: Color Fill A Range Of Cells If Specific Cells Not Blank

Feb 7, 2013

I am using Excel 2010 and basically i am trying to fill a range of cell with a green color if any value was enter in a specific cells. Example: I would like to fill range: A10:c13 with a green color (regardless of the cells content in this range) if a value was entered in cell C10 or C11 or C12 or C13.

I've tried conditional formatting but unfortunately I'll have to apply formatting for every cell and for a range of over hundred cells is not efficient.

View 7 Replies View Related

Excel 2010 :: Return Min Date In Range Where Adjacent Range Is Blank?

Feb 7, 2014

I am working in excel 2010. I have a tracking document that lists free tickets and their expiry dates. In the adjacent columns we track redemption details of these free tickets. What I want to do is return the oldest expiration date from A only if the ticket has not been used (i.e. B is empty). This will allow me to see the date the upcoming tickets about to expire so we can make sure they are used.

A________________B
Expiry Date________Redeemed by
15/08/2014
15/02/2014
15/08/2014________John
15/02/2010________Marc
15/02/2011________Bob

View 4 Replies View Related

Excel 2010 :: Shifting Cells Left And Then Up If Left Cell Is Blank?

May 8, 2014

I have a 2010 excel sheet containing 14 columns and 45082 rows in total. I am quite illiterate when it comes to writing macros but I know that what I need can be achieved with a set of codes.

To be more clear, I inserted two tables below. The first one represents the current data structure, and the second one is the way I want my data to look like.

Current data structure looks like
Variable 1
Variable 2
Variable 3

[Code].....

View 9 Replies View Related

Excel 2010 :: Access Data Connection Query - Missing Data All Of Sudden

Oct 19, 2012

I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.

btw...I am running Office 2010

View 2 Replies View Related

Excel 2010 :: Click On Pivot Chart Data Point And Display Data

Apr 22, 2014

Pivot Chart. I would like to set up something to where a user can click on an individual value on a pivot chart (currently a line chart set up with 4 data series) and somehow display some underlying data. I have a lot of information stored in a data worksheet that I can't display all at once, but if a user sees a questionable data point, he/she can click and learn more about it from source data, or even a new query of the data worksheet.

I am using Excel 2010

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved