I currently have a workbook with 6 sheets, the main worksheet has roughly 10000 rows rows (with is the one Im having trouble with) up until a few days ago scrolling wasnt to bad but just recently it is really struggling to scroll (no changes to workbook)
I have a quad core Q6600
4GB ram
plenty of HDD space
the excel doc size is 2.8MB
is there anything I can do to improve scrolling on large spreadsheets?
I have a large Excel workbook (about 70,000KB) and it takes ages to load and is slow to operate.
Is there anyway of compressing it or making it run faster and not slow down the computer?
I heard there is an efficient way of writing large amounts of data at once to a worksheet. My program is writing alot of data (e.g. 600+ items in each row) and takes over an hour to run because it writes to each cell one-at-a-time. I seached this forum and couldn't find the answer. Here is an excerpt from an old 1998 article by Dermot Balson and William Mercer that used code to write to cells in large chunks but it doesn't work for me - I get a method failed message.
'to read in
Dim A As Variant 'MUST be variant, no brackets
A = Range("SomeRange").Resize(10,20) 'reads 10x20 array starting at range SomeRange
'(NB I've used Resize above but you can specify a range of cells any way you want)
'to write back to sheet
Range("SomeRange").Resize(10,20) = A
'A can be any data type but MUST be two dimensional even if you are only writing one
'column or row - first dimension is used for rows, and the second for columns
'this can be slow - see third question below for workaround..
Here is the link to the article: http://www.avdf.com/apr98/art_ot003.html. A more detailed explanation how this trick works
I have a spreadsheet containing a group of Macro Buttons, each button has been assigned a macro that enters specific text in the selected cell when the button pressed.
But as you scroll down the sheet, the group of buttons is left above. Is there a way to make the buttons scroll down (or up) along with the spreadsheet ?
I have an Excel sheet that displays 50 rows and 20 columns when it is opened the first time. These rows and columns are the only ones that I want the user to be able to see. The user shouldn't be able to navigate the sheet by using the scroll bars or the wheel on the mouse. I have of course already removed the scroll bars but I don't know how to handle the scroll wheel problem.
View 2 Replies View RelatedI am copying a large sheet into a spread sheet. The problem is one of the columns contain numbers with some cells having an asterisk at the end of the number. This causes problems with the formulas on other sheets that use this data. Can I use a formula or what is the best way to eliminate the "*".
View 4 Replies View Relatedway to copy certain cell ranges from a main table into a different sheet (for nicer printing output, as in the main table there are also unused ranges) and in such a way that they would be copied there one after the other with no spaces between them.
( I have say A1:M1 with some cells for labels,
then A2:M4 with a smaller table with some user choices etc. etc.
then again A5:M5 with cells for labels
and A6:M8 with another smaller table with user choices... )
multiply by 2x
Then I want to copy just those ranges that the User has selected something in - e.g. only A1:M4, if he selected something in A2
or A5:M8, if he has selected something in A6
I am currently working on a very large spreadsheet with a current size of 94mb. So obviously I have problems running and using it.
My question is a general one, I need to understand better why a spreadsheet gets so large. It has 55 tabs with the largest having 1000 rows. It also links in a number of places between sheets but also to external locations. I have moved all files that it is linked to in to the same file.
I am at the point where I am thinking about starting again! And I was wanting some tips or even a recommendation of a book that could help me optimize my spreadsheet going forward so to increase the speed but also reduce the size.
i usually work with 3d and i export all my working in excel to summarise in a table.Now i came across an issue whereby i have a whole exported list for the whole project and i want to extract some of the info to make a new table.My master list is a dynamic one it keeps on updating from the project.
I have attached the sheet, there is one master sheet and 2 other sheets, PID10 & PID2...those sheets are break down table form the master sheet.
I I have a excel document which has a sheet containing a large number of array formulas.
I turned the automatic calculation off.
However, even when I press F9 to calculate the cells it just crunches, gets stuck at “Calculating 0%” and then crashes.
The majority of the array formulas are IF queries with multiple criteria.
Would it speed up the calculation if I didn’t use array formulas?
I am trying to dynamically set my print area and page breaks on a large sheet.
Basically I want to get the last column and last row in used in the sheet. And to create boxes (24x30) until I get all the way horizontally and vertically.
I have a large amount of data in a single spread sheet. Each row has a branch number on it, there are multiple branch numbers. Each branch number is located in Column A. I want to separate this sheet and put all of the branch numbers into their own sheet. how to do this without manually copying and pasting?
View 9 Replies View RelatedI'm copying large amounts of data from one full sheet in one workbook to the sheet in another book.
Everything is now automated, except that it asks in between books if I need to keep the Clipboard data.
I've got a 80 Mb CSV file and would like to open and work with it. Too many lines (90000 or so).
Is there a way to split ( ) this file so I can open two files instead?
I've created a worksheet with split panes. I'm able to scroll through my data vertically and horizontally while keeping one area of th screen stationary. Here's my question:
Is there a way to limit the movement of my scroll bars so that it can't move beyond the viewable portion of my worksheet? In other words, I want the scrolling to end when there is nothing else to see but a field of empty cells.
way of getting two sheets to scroll together, so when I scroll one window the other one moves too? Some text editors have that future (IIRC UltraEdit does)
I can't find a scroll event in the windows code to trigger a macro to run itself.
I can get around it by using a selection change event which would restrict me to moving around with the cursor keys/Pageup/down, but I've got 15,000 to review regularly in a short space of time and it would be quicker by mouse
[code/]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'This macro scrolls the Report to TopLeft when you click
'anywhere in the Report columns
If Target.Column > 16 And Target.Column < 22 Then
With ActiveWindow
.ScrollRow = 1
.ScrollColumn = 15
End With
End If
If Target.Column >= 1 And Target.Column < 15 Then
With ActiveWindow
.ScrollRow = 1
.ScrollColumn = 1
End With
End If
End Sub[code/]
...but I cant figure out how to scroll it down the list to the NextBlankRow-20 such that the previous 20 lines of data are shown to the top of the screen.
Is it possible to use a macro that do the following:
scrolling, only the used range + 1
So if the used range is B2:D12 then the scrolling is possible to row 13 and column E
I want to return the value of E2 from sheet, "ALL INFO" and paste it in B2 on my current sheet. When I scroll this formula to right, I want the next to be the value of J2 from sheet, "ALL INFO," etc.
View 2 Replies View RelatedIve seen on someone elses workbook the first 6 columns are fixed (always in view) then you can still scroll across but those first six columns always stay in view.
View 3 Replies View RelatedI'm looking to disable the horizontal and vertical scrolling in 1 worksheet
Am i able to do this?
I'm having a problem working with a protected document made by another user.
The sheet has a vertical split at column F, and when I use the arrow keys to scroll right beyond this line (ie from column G and beyond), when I reach the last column (BB), it just loops straight back to the beginning (column F). This also happens in the same direction.
How I can stop this happening, so that it just stops when it reaches the end of the document?
There doesn't appear to be any coding on the sheet.
I have a spreadsheet where I want to put notes in, but I don't want to re-size the cell as it will ruin the format. The amount of information will vary from entry to entry, each entry into the sheet will have its own row and within that entry I want to dedicate one column's cell for that entry's notes. Is there anyway to make a macro for a scroll bar for individual cells so I can scroll though the text entered in that cell. I have tried a text box but the text overflows once the amount of text over fills the text box.
View 4 Replies View Relatedi want to know whether there is any way to make an MouseWheel-Event in VBA (Excel 2003) and send it to the scrollBars of my UserForm?
View 3 Replies View RelatedI want is a form that has three different partsof it. Each part containing checkboxes, a spreadsheet, textboxes and labels. But I only want to display one of these parts at a time, depending on what the user clicks.
I know I could write code to move everything into the visible part of the form but that would be messy. I hoped I could find some way to instead move the visible part. Like if I had scrolled.
An example. Say the visible form is 100 high and 100 wide. When opening you see from the width 0-100 and height 0-100 (pixels or whatever those things sould be called). Upon pressing a button I instead want to see width 101-201 and height 101-201.
I am sure this has been asked -and answered- before.
I need to select a cell, e.g. C1000, and change the view, so that I see C1000 on top of my spreadsheet.
range("C1000").select clearly does not accomplish what I want.
What is the vba code to disable scrolling Up or down?
View 1 Replies View RelatedIs there a way to limit the number of columns and rows that a user will see and therefore limit the scrolling in a spreadsheet?
View 6 Replies View RelatedI have a worksheet with a column("A:A") of about 1000names. The user will start at the top and work down, entering stuff into other columns for some ofthe names. My need is: How do you automatically scroll, with code, say row 48to the top of the worksheet when the user reaches row 48 with?.
View 2 Replies View RelatedI have a sheet in which I want to limit the scrolling area to B2:B60 & D2:H60, in other words I don't want to include neither A2:A60 nor C2:C60 in my scrolling area.
Is there a way to do this?
I know I can lock the cells, however, pre-existing code will not work if cells are locked.