See Hidden Rows With Scroll Bar In 2003
Dec 16, 2008
I have a worksheet where rows 22 through 52 are allotted for invoice information. Now, not every job is going to have 30 invoices, and I'd like to be able to hide 25 of those rows and have a scrollbar for that section of the page so that when a) user needs to enter invoice info they can scroll to an empty row, and b) when reviewing the data user can scroll through and see it all. The rows above 22 and below 50 are job info/totals and need to remain visible.
Early attempts to solve this conundrum resulted in a scroll bar that was capable only of changing the date entered for the first invoice (Date is the info in A22, which was the linked cell).
View 9 Replies
ADVERTISEMENT
Nov 18, 2011
I was working on an Excel 2003 workbook with 3 worksheets. The first sheet is the 'Menu' sheet on which I placed buttons to take me to the other two sheets and back. I wanted to hide the Excel menus, toolbars, scrollbars, etc. on the 'Menu' sheet so that no user can alter that 'Menu' sheet. The 'hiding' code was not meant to affect the other two sheets.
The code worked fine. However, when I closed the workbook and tried to open a NEW workbook so that I could do some other work, I realised that the new workbook and its accompanying sheets had the menus, toolbars, scroll bars, etc. hidden. I closed the workbook and tried opening a new workbook. It was the same experience.
I called up the file with the code, deleted all the codes attached to that file, and re-saved the file. It did not solve the problem. And when I tried many more new workbooks and got the same problem, I deleted my entire Office 2003 installation and re-installed Office 2003. It still did not solve the problem.
View 9 Replies
View Related
Oct 14, 2006
I have a workbook that contains a form that requires the user to enter a valid username and password to open the workbook. On my laptop it runs perfectly. On my desktop I have issues. When I launch the workbook on my desktop, I get the userform login as I am supposed to. Once I enter the login info I get the following error:
"Compile error in hidden module: UserForm1"
My desktop operating system is as follows:
Windows XP
Professional
Version 2002
Service Pack 2
And I am running the following version of Excel:
Microsoft Excel 2003 (11.8105.8107) SP2
Part of Microsoft Office Professional Edition 2003
Once again, it seems to work perfectly on every PC except for mine. I need to be sure I have no compatability issues. Please help. I can post the code if need be.
View 9 Replies
View Related
Jun 18, 2012
I have a 2003 Workbook that is around 7 mb. It used to be half of that but it jumped up in size which makes it harder to email, etc.
On the front page it would go out past the (known) data and I was able to reset it to the correct place but on the 2nd sheet it would go to IV 65536 without fail. I used VBA as well as physically deleting andor clearing rows and columns but to no avail.
I decided to start eliminating portions of the workbook to see if I could find where the size was coming from.
1. I deleted Regular formatting
2. I deleted Conditional formatting
3. Made everything the same font name and size, etc
4. I deleted all VBA Code.
5. Last but not least I deleted/cleared all data from all the sheets
Between each change I would save and close the workbook and check the size in Explorer.
Each deletion brought down the size, however after finishing step 5, I had a blank workbook with 3 sheets and it was still over 1.4 meg!
The next step was to remove the 2nd sheet. I did this and it went from 1.4 meg to 25 kb.
At this point I have been coping all the info into notepad and the from notepad into a new workbook.
View 2 Replies
View Related
Nov 20, 2012
The other day I inserted a row in a shared workbook and then saved it. On my screen, the inserted row was visible and was the correct row height of 12.75. My goal was to insert the row so another user sitting next to me could enter information in the new row.
After I saved the workbook, and after the user sitting next to me saved her workbook, the row I inserted should have appeared exactly the same on her screen, but it did not. Instead, the inserted row showed up as a hidden row on her screen. It was there, but the row height was collapsed to the point of invisibility.
I repeated my attempt to insert a row which the other user could edit several times, but each time I got the same result. I could not make the row show up on her screen with a row height of 12.75.
Shared workbook in Microsoft Excel 2003?
View 1 Replies
View Related
Nov 5, 2009
If is possible to create any info about freezing panes. I mean, when is pan freezed, and rows are scrolled in lower part of table (upper data are not vissible) if is possible display/show any text about this (e.g. "The first line of table are not visible")...but only if is not the upper part of data visible..not always.
View 5 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
Sep 25, 2012
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies
View Related
Mar 4, 2014
I have an excel file with a table in it. It contains 2051 records (attached). This is just a sample, the original file has around 30,000 rows.
When I start using filters, I run into problems:
Step 1: Filter by Unit, condition (e.g.) Unit_23
Excel shows in the status bar the following message: 437 of 2050 records found.
Step 2: If I scroll to the bottom of the table, the row numbers are colored in blue (normal for a filtered list) however the last row is not colored and it actually should not be shown since its unit is not what I filtered for (its unit is Unit_25)
Step 3: Clear the filter of Unit
Step 4: Sometimes (depending on what I filter for), one or more of the bottom rows are hidden!!!
Attachment 301726
View 4 Replies
View Related
Apr 18, 2013
I have data in B4:B55 and need a formula to return a count of rows, including rows that are blank. However, there are hidden rows that need to be omitted from the count.
View 1 Replies
View Related
Mar 15, 2013
I wonder if Excel allows scrolling many columns or rows at a time. That's right, I have a worksheet with frozen panes by "D2" cell; then there are sets of 4 columns for each day of the month (for example "D:G", "H:K" and so on). I need when I scroll horizontally, that every first column of the set to snap at the edge of pane at once. In other words, is any elegant way that usual scrolling pace of one column/row at a time to be changed to 4 at a time?
View 3 Replies
View Related
Mar 18, 2014
My spreadsheet has 600rows and the vertical scroll bar ends at row 459424. I have attempted to delete all of the extra rows by holding SHIFT and CTRL, striking the down arrow then right-clicking on the left margin of highlighted row numbers and choosing delete.
But the unwanted rows are still there and my vertical scroll bar is not correlated to the 600 rows.
View 1 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
May 29, 2014
I am trying to use the SUMIF function to create a formula that will read the visible cells in Column B and if any visible cells in Column B are equal to or greater than zero I want Excel to sum the corresponding values in the visible cells of Column A. Not so hard but I'm having a difficult time getting Excel to ignore data in hidden rows.
See attached file for and further explanation : Sample.xlsx
I've tried using SUMIF as well as the AGGREGATE function but no success, yet.
View 5 Replies
View Related
Jan 24, 2014
I have a spread sheet which has a number of protected cells so that they user can tab only on required cells to enter data. At times some of the rows are hidden however pressing "TAB" will continue to tab through on the cells within the Hidden Rows. Is it possible to only TAB through Cells which are visible?
View 1 Replies
View Related
Sep 5, 2009
I have a worksheet with a macro as follows: ...
View 6 Replies
View Related
Nov 19, 2006
I need a formula for counting rows. It should achieve the following;
It should count in increments of 1 (1,2,3,4, etc.) in each cell in a column (column AW, to be prescise).
It should skip hidden rows.
It should account for the fact that a formula is able to reveal rows and when this is
done, the counting formula should adjust to count the newly revealed row.
It should also be able to do the opposite - another formula/macro hides rows, and when this happens it should not count the newly hidden row.
I can imagine a formula in each cell of the column that says "Check the previous column and if it is visible, add 1. If a hidden row is encountered, do not add 1. When a non-hidden row is encountered again, continue adding 1."
View 9 Replies
View Related
Dec 15, 2009
I have a worksheet with hidden rows and Page Breaks. I know that the page breaks are causing my worksheet to print blank pages where I have page breaks and hidden rows.
I need the page breaks or the form just prints a mess... I tried eliminating them - but that didn't work at all.
I need a macro, which will examine the worksheet, look for the hidden rows, exclude them from the print area, and then Print the worksheet.
View 9 Replies
View Related
May 6, 2006
Why do I get a "sub or function not defined" error on the first line? The problem seemed to arise out of nowhere. The sub is located in module 1. I call it with "Call DeleteHiddenRows" in sheet1 inside of a "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" event. Should I be adding some declaration somewhere (some "dim" line?)???
Sub DeleteHiddenRows()
For j = ActiveCell.SpecialCells(xlLastCell).Row To 1 Step -1
If Rows(j).Hidden Then
Rows(j).Hidden = False
Row(j).Activate
Selection.Delete
End If
Next j
End Sub
View 4 Replies
View Related
Jan 18, 2007
I'm completely at a loss I have a worksheet with hidden rows which I do not want to print yet Excel prints them anyway resulting in 12 pages rather than just the pages I want.
View 7 Replies
View Related
Mar 21, 2007
i count number of all rows with:
Dim countall As Long
countall = Sheet1. Range("a1", Sheet1.Range("A65535").End(xlUp)).Rows.count
this is the total of all rows, including hidden. how do i count the hidden rows which have been filtered?
preferably not a loop, isn't there a way to use "entirerow.hidden" and count that?
View 5 Replies
View Related
Jun 7, 2007
I am trying to create a macro to unhide only a row at a time, from around 150 hidden rows altogether. It is basically to give the effect of adding extra rows to a "table" that a user could then input with new data (I have to do this way according to other set-up in the workbook), but this isn't very important to do with this question. When I do a Format<Row<Unhide from the Excel menu, it always unhides ALL of the hidden rows. I have fiddled around but can't seem to find a way to avoid this happening, all I want is for just one row to unhide. I thought it might depend what cell/s I had highlighted, but I haven't found a way that makes it work.
View 7 Replies
View Related
Aug 21, 2007
I HID 4 rows using this help section. (Format - Hide)
Then, I modified several column widths simply by placing my curser at the top of the column and pulling it smaller or larger.
(I don't know if this did or did not cause my problem) which is simply that - I am now unable to find my hidden rows.
I know where they are supposed to be, but when I highlight them and click (Format - Unhide) nothing happens.
I tried (Find and Select - go to special) and no white bar shows up.
Can sombody please tell this frustrated Newby How to find the rows I just hid 1 hour ago?
If I posted in the wrong place, I appologise as I spent more time trying to figure out where and how to post than I did trying to solve my hidden row problem.
View 5 Replies
View Related
Sep 10, 2007
Is there a way to keep the Sum Funtion from adding in the values from Rows you have hidden? I want my total to be the result of only the visible lines, but have reasons for hiding rather than deleting rows (I may have to unhide some later depending on other factors)
View 9 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
Aug 21, 2014
Is there a way to copy and paste a sheet from one spreadsheet to another without getting the rows that have been hidden? I have a database with about 800 rows and another 150 or 200 scattered through it that are currently hidden. For what I need right now I don't want any of that hidden data. Do I have to manually delete it or is there a way to ignore it (I thought of paste special but I can't find one that works).
View 2 Replies
View Related
Jul 21, 2014
I was able to create a macro that allows a user to unhide rows in a protected worksheet without unprotecting the other locked fields (see below). My question is: I want to add to this command so that 1 row can not be "unhidden" if the previous row is still hidden. For example, row 25 can not be unhidden if row 24 is still hidden.
Macro command used:
Private Sub Commandbutton4_Click()
Sub Hide_Rows2h()
ActiveSheet.Unprotect Password:="xxx"
Rows("25").Hidden = Not Rows("25").Hidden
ActiveSheet.Protect Password:="xxx"
View 3 Replies
View Related
Jan 29, 2014
I have a spreadsheet with 31 rows and 8 columns that are completed as a checklist.
I have a macro that currently increments where required and also pastes the 31 rows again after
What I am looking for is some code whereby if I run a Macro the previous 31 rows are hidden
View 5 Replies
View Related
Mar 23, 2009
My workbook contains 10 worksheets. Some users will use all 10 worksheets; some will use only 5 worksheets. If a user is not using a worksheet, I would like to hide it but leave it in the workbook.
However, I want to keep all workbooks in sync even though a user may choose to not use a particular worksheet. When my macros encounter a hidden worksheet, they stall. Does coding exist that allows a Macro to run on a hidden worksheet without making the worksheet visible?
View 2 Replies
View Related
Sep 27, 2011
Is there an easy way to determine which rows in a spreadsheet are hidden, rather then scrolling through manually and trying to find nonsequential row numbers?
View 1 Replies
View Related
