Scroll Bar Exceeds Data Range
Jul 19, 2010
My worksheet ends at cell AG650, and Control-End takes me there. I have deleted all columns and rows beyond that, saved, closed and re-opened.
But the scroll bar takes me to cell AG924500 -- more than 900,000 rows too far...
I tried setting the Scrollarea in VBA and got some unexpected results.
Private Sub Worksheet_Activate()
ScrollArea = "A1:AG700"
End Sub
The elevator bar did not shrink at all, and the visible portion of the worksheet reacted inconsistently, sometimes correctly, sometimes seeming frozen or stopping short of the 700 rows specified, which makes me think there's an Excel bug involved.
I've also tried Hiding the unused rows but get this message:
"Cannot shift objects off the sheet"
So I've gone into VBA and removed all objects using this code:
ActiveSheet.Shapes.SelectAll
Selection.Cut
But with no change at all...
I've also applied several commercial 'Excel tuning' programs, to no avail (FastExcel and CodeCleaner)
View 3 Replies
ADVERTISEMENT
Oct 5, 2007
column A4-A9 equals the sum on A10. What I am trying to do is put a formula in H10 that will start out at 35 from no addition, but if cells A10-G10 go over 22 I want it to subtract from H10.
View 5 Replies
View Related
Mar 4, 2008
******** ******************** ************************************************************************>Microsoft Excel - Rod Extract Test Master.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutCR1=CRCSCTCUCV1Loan NumberCollect Tax EscCollect Oth EscPay Int on EscRE Tax Monthly Const2304100830NNY$0.003304100830NNN$0.00Borrower,Master,ARM [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
What I need to have happen is that the user decides what duplicate row to delete. The reason that there is a duplicate is that Row 1 has something different from Row 2. In my example above, CU1 is different from CU2. This is what caused this loan number from CR to be duplicated. I need to delete at my discretion one of the 2 rows.
What I would like is to have the user Select Range CR1:CR2 (this is relative in the sheet), run the macro, have it select the 2 row range, compare (relative) B1:B2 are they equal, compare C1:C2, are they equal, etc through the rest of the range. I would like the screen to update and stop with the range highlighted that is and and a MsgBox to click ok to Continue, then continue through the rest of the range. Once it has finished cheking the range, can the user use the mouse to select the range from CR that needs to be deleted, have the appropriate range selected and then the MsgBox Is this the range you want to delete? Y/N........................
View 9 Replies
View Related
Nov 26, 2007
I am using the toolbox/ properties method to restrict the scroll area in the sheets of my workbook. However, for some sheets when i keep the right hand arrow pressed, the screen will not scroll to the last column. (i.e. the screen display will not move with the selected cell box)
View 2 Replies
View Related
Jan 13, 2007
In the menu via TOOLS-->OPTIONS-->VIEW I am trying to remove the scroll bars from just one worksheet but when unticked, this option affects all of the worksheets in my workbook. Is there a way to just nail this selection down to one sheet via properties?
View 2 Replies
View Related
Dec 3, 2012
I have a single sheet that has 6 fixed columns of data in but the number of rows can vary per user per import. Users can actually insert rows as required. The seventh column has my macro buttons present. It looks and works great for about 20 rows (one screen) full. If the number of rows gets to be more than that and the user needs to scroll to modify those lower rows the macro buttons roll off the top and are not visible to access. Is there a way to keep the macro buttons in the same location (column G) and only have the data (columns A-F) scroll when required by the user? This way the macro buttons are available on the rows beyond the first 20 or so for the user to use, instead of having to scroll back up to use them. I've tried using the split and freeze functions but neither worked.
View 5 Replies
View Related
Jan 22, 2014
I followed the instruction in the TechRepublic article "10 steps to creating a scrolling Excel chart"; [URL].... The problem is that the scroll bar stops working at a certain point and will not bring up the last 8 months of year 4 of my data. I get error messages and the chart turns blank.
I also tried a different method (again for a scroll bar and a rolling chart) as outlined in [URL].... There are a number of similar articles with almost identical formulas. Here, the first 8 month of year one are inaccessible = error messages and chart turns blank.
I have 4 years worth of temperature data. The original data table from where the Index function draws the data is 1463 rows long (minus 2 rows for the header). The "magic" number where the scroll bar stops working is 995 for the current value. From a current value of 994 onwards, the entire matrix displays error messages "Ref#".
In the second case, the magic number is 1242 which equates to August of the first year. 1241 and lower for the current value will freeze the chart.
How the scroll bar control generates and gets hung up on either of these two magic numbers is a mystery to me.
I set the control properties at the beginning, i.e. Jan 1 of the first year to: Current value =1, Minimum =1 (I also tried 0), Maximum = 1500, Incremental =5, page =30, linked cell =$E$2. What is most puzzling to me is the fact that the scroll bar does work but only for part of the data set.
View 1 Replies
View Related
Dec 1, 2005
My problem: get a cell to produce a pop up message when it exceeds a
certain value and do not restrict the entry value?
I tried Data - Validation but have had no luck.
The details (much simplified) are these:
I want to multiply B2 x A2 and if the answer exceeds $2499 have a pop up
message appear. Is it possible to have the pop up ocurr while inputting data
into B2 or only after "entering" or when B2 has been reselected.
A B
1 qty unit cost
2 1 $1250
WHAT IS THE METHOD OR FORMULA?
View 11 Replies
View Related
Jul 8, 2009
There is probably a simple solution to my problem that I am just not seeing because I cannot believe I'm the only one trying to pull out the number of hours after summing a column of times when the total exceeds 24 hrs. I have attached a sample spreadsheet which has columns for start time, end time and time used (ie end - start time) Originally when I totalled the column of time used, I got 20:27 hrs instead of the correct 44:27 hrs. After doing some research I found out that I had to create a custom format of [h]:mm to get it show beyond 24 hrs. However, now that I can see the 44 hrs and 27 min as 44:27, I cannot seem to pull out the 44 hrs to use it in a calculation of multiplying total time against a charging rate ($/hr) to get a total cost in $. The sum using [h]:mm is in cell F68 and I was hoping to convert this [h]:mm into a number of hrs as a decimal (ie 44:27 to 44.45 hr) by =((HOUR(F68)+MINUTE(F68)/60)) but I get back to the 20 hrs of a h:mm format.
I was originally surprised that I couldn't sum a column of times and get the total time without creating a special format. But I am really surprised that it isn't intuitive to pull out the correct number of hours when it exceeds 24 hrs. I am sure that it's simple because it seems something that a lot of people would have going on in accounting type of spreadsheets. I am going to be doing a lot of summing times coming up and would appreciate suggestions. This forum helped me once before and I hope for the same results!
View 8 Replies
View Related
Sep 23, 2008
I have an excel, in which the user can input values and the sum will be computed for each column (i.e. =Sum (L2:L29)) . I want to alert the user when the computed sum exceeds a particular value, else it shud proceed.
It can also be done at while the save & close of the workbook.
View 10 Replies
View Related
Oct 3, 2009
If I already have lets say 90% on B9 and I try to enter 15% on D9 it wont allow me because of the total being over 100%, but how can I have an alert to say Please enter 10% or less.
Or
If I already have lets say 50% on B9 and I try to enter 20% on D9 and I try to enter 50% F9 it wont allow me because of the total being over 100%, but how can I have an alert to say Please enter 70% or less (the sum of B9+D9).
Can this be done using VBA?
I've attached the file that has the data validation.
View 11 Replies
View Related
Mar 28, 2011
Say I want to create a proxy hyperlink like this:
=HYPERLINK("http://www.google.com/search?q="&B3&...ctive","Search Google")
but the URL address I'm going to use is way too big for the cell (I think max is 255 characters).
View 4 Replies
View Related
Jan 23, 2007
I get my website's log files in a raw text format, delimited with hyphens.
That much I can deal with.
What I am having problems with is the fact that there are over 65,536 lines of data per log file (where each line represents one " event" in the history of the site), and therefore Exel is unable to import the entire log.
View 9 Replies
View Related
Jun 9, 2008
I have 4 columns wherein the details are somewhat given below. Is there any way, wherein the figures under the column "No. of times" can be circled if it goes above 3.
Sheet1 CDEF19DateTimeMin.No. of times20Apr-0821Wed - 09-Apr-20082:42 & 2:494 & 23222Thu - 10-Apr-200803:215123Fri - 11-Apr-200810:27, 7:24 p.m., 7:39 & 7:401, 8, 1 & 12424Tue - 15-Apr-200810:26, 10:31, 10:32, 11:26 & 11:306, 2, 4, 4, 25 Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Jan 7, 2010
I'm trying to do a few things actually. I am somewhat a newb at programming, although I do understand how C++ and the coding works within Excel. To me, it's like the English language...knowing all of the vocabulary and formatting it right is important. If I don't know the vocabulary, I don't know what options I have.
This tool is being used in the call center I work in. It is to help eliminate repititious information used to notate accounts. I have attached a picture of what the tool looks like so you can have a better idea of what I'm working with. Here are a list of things I'm trying to do which I have searched and not found answers to:
1. I am trying to use a middle mouse scroll to scroll through validation list. This list information is located on a different locked tab. Basically, I have a few rows where a drop down selection can be made and it would be easier to scroll rather than manually moving the slider within the validation list.
2. As you can see, this "tool" an employee and I have created has many buttons. What I would like to do is to be able to click a button on the right and have it "stack" text within a single cell. In other words, if I click the button once, the text may be entered into the "actions taken" cell. If I click another button, I can have the option (or maybe a little "+" next to the button) to add this button's information to the end of the information already entered within the "actions taken" box. I've tried recording and using the keyboard and selecting "end" and then having the text added, but it didn't work. I've seen "loop" VBA code, but I don't want something to repeat, I want it to stack in the cell when clicked manually.
I'm sure there is a way to make the tool much better using VBA code and not use Excel, but I don't have an extensive knowledge of Excel to do something like that. I'm a very fast learner and understand how the coding works, so if you have an idea which may help, and you understand what I'm trying to do here, please provide your .02.
Basically, it's used so the Customer's name and phone number are entered manually, and then just about everything else is automated by the use of the buttons on the right. If I could get the 2 above options to work, it'd be perfect. I just figured out I could eliminate screen flicker and hide the macros working by using:
View 12 Replies
View Related
May 14, 2014
The user enters data into Column E on Sheet1 and i want my code to display a pop-up box when a cell's value exceeds 500. I've tried the two codes below which i thought would work as Excel didn't highlight any breaks when i wrote the code, but no pop-up box is being generated when values > 500.
ATTEMPT 1:
Private Sub Threshold_Check2(ByVal Target As range)
Dim cell As range
For Each cell In ActiveSheet.UsedRange.Columns(5).Cells
If cell.Value > 500 Then
MsgBox "Value within 15% of Threshold"
Next cell
End Sub
ATTEMPT 2:
Sub Threshold_Check(ByVal Target As range)
Set Target = range("E1:E150")
For Each cell In range("E1:E150")
If Target.Value > 500# Then
MsgBox "Value within 15% of Threshold"
End If
End Sub
View 5 Replies
View Related
Sep 11, 2006
Apart from the obvious restriction imposed by the virtual size of a spreadsheet,are there any other factors that would induce a problem with size. I have a set of data with 3000 rows and 15 columns. I would like to organise this using 5 of the data columns as rows in the pivot, 1 as column and 1 as data.
I have a number of sets of data which work perfectly, but one set, the largest, fails when I attempt to add the data field.
View 3 Replies
View Related
Mar 21, 2008
I had more than 7 nested if statements so I attempted to formulate the named ranged formula. My 2 ranges are as follows: LDD1=IF(MONTH(P1)=MONTH(F2),C5-F5,IF(MONTH(P1)=MONTH(G2),C5-G5,IF(MONTH(P1)=MONTH(H2),C5-H5,IF(MONTH(P1)=MONTH(I2),C5-I5,IF(MONTH(P1)=MONTH(J2),C5-J5,IF(MONTH(P1)-MONTH(K2),C5-K5,IF(MONTH(P1)=MONTH(L2),C5-L5))))))) LDD2=IF(MONTH(P1)=MONTH(L2),C5-L5,IF(MONTH(P1)=MONTH(M2),C5-M5,IF(MONTH(P1)=MONTH(N2),C5-N5,IF(MONTH(P1)=MONTH(O2),C5-O5,IF(MONTH(P1)=MONTH(P2),C5-P5,IF(MONTH(P1)-MONTH(Q2),C5-Q5))))))
Basically what it's doing is comparing the current month with the month in the column heading and calculating appropriately. However, when i input the if statement (=if(LDD1,LDD1,LDD2), i get an error saying that it's an inconsistent formula and the result that it gives is wrong, too. Problem cell is e5
View 2 Replies
View Related
Aug 30, 2006
I wrote a Macro that was running up to now searching for information. Now the text file is bigger than the 65536 limit in Excel. How can you import the .txt file over two or more Sheets using a Macro?
View 2 Replies
View Related
Jan 20, 2012
Excel 2010 Higher than past average formula?
Im using Excel 2010 and want to make a formula that will High light the number when it exceeds the past 30 days average. My information is listed vertically in row F..
View 3 Replies
View Related
Dec 21, 2008
I'm sure this can be done but I don't think I have the formula correct. What I am trying to do is add each day's total while displaying a positive number if the total exceeds 6000.
View 5 Replies
View Related
Mar 27, 2014
I've set up a filing system which saves sheets/ workbooks based on the value of a cell - Range("B1") Everything works great apart from when ThisFile String length exceeds 31 characters which you may know is the max useable character length for a sheet name - I had no idea! 8-0
Is there a way i can check if string length exceeds 31 characters then, if it does, shorten it to 31 characters?
[Code] .....
View 2 Replies
View Related
Jun 19, 2007
i have managed to pull together some code that will deny people adding data into cells if they have 5 of the same entry. the entries are entered in a range and are matched against a single cell outside of the range. heres the
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim greycell As Range, i As Long
If Not Intersect(Target, Me.[grey]) Is Nothing Then
Application.EnableEvents = False
For Each greycell In Target
If WorksheetFunction. CountIf(Me.[grey], greycell.Value) > 5 Then
i = greycell.Interior.ColorIndex
greycell.Interior.ColorIndex = 3 'red
greycell.Select
MsgBox "no cell entry past 5", vbCritical, "ERROR"
greycell.ClearContents: greycell.Interior.ColorIndex = i
End If
Next
Application.EnableEvents = True
End If
End Sub
what i need with is adapting this code to match two ranges as i cant use the worksheet_change event twice. i need it to be as if they were seperate events but are merged together. eg:.............
View 3 Replies
View Related
Aug 1, 2013
I am attempting to make a range of cells' data be deleted if a cell outside of that range that had a name in it is no longer there. So if I have a list of names in cells A1:A6 and a range of data in cells D10:E20 that corresponds to a name in cell A2. If the name in A2 is deleted I would want the data in D10:E20 to be deleted.
View 9 Replies
View Related
Nov 19, 2009
I am trying to make a scroll box so I can enter data into the scroll box dirrectly. Such a simple thing.
View 11 Replies
View Related
May 13, 2013
I'm trying to scroll down my work sheet but it's not letting me. Every time I move the scroll bar down the page it just keeps showing me the same part of the sheet eg cells 1-30 when I want to see all the way down to the bottom. I have some 20,000 lines I need to scroll through but I can't see all of them. To add to this, I keep having lines hide between other lines so theres a big gap like going from line 120 to line 4000 and I have to manually unhide them every time to see them.
View 2 Replies
View Related
Nov 14, 2008
Does anybody know how to force the scroll bar on a listbox to be at the bottom rather than the top when filled with data?
View 6 Replies
View Related
Dec 26, 2008
I have a spreadsheet with some scripting that automatically emails the report to whoever is on the sheet. I'm not sure what kind of scripting it is. I think my boss found it somewhere.
Whenever I scroll either down or up, the first 3 rows disappear and I'm not able to scroll back up. The scrollbar shows that I'm not at the top, but it won't let me go back up. The first 3 rows contain the button to send the email. If I close out without saving and open it again, its fine, until I scroll up or down.
View 4 Replies
View Related
Aug 23, 2009
I've been trying to set the scroll bar min/max/small change to a decimal (percentage value) within the properties, obviously this does not work.
Since users can enter a value in the link cell for the scroll bar, how can I modify the current min/max/small change via macros so the scroll bar will show a min on 1%, small change of 0.25% and max of 25%?
View 14 Replies
View Related
Apr 24, 2007
Simple VBA What is the command to scroll to over "x" number of columns? and/or rows?
I just the active cell to move over 5 cells to the right for example?
View 9 Replies
View Related
