I have a spreadsheet that has a range of a2 thru j62. I want to scan column B and if it is a 0, I want to hide the row and set the print area to the a2 thru j62 range and print the spreadsheet with the '0' columns hidden
View 2 RepliesI have a spreadsheet that is linked to another spreadsheet in a workbook. The information comes from an export of an access query into a template in excel that I am using just to store the values, then I link the values to the appropriate field in another sheet. I was wondering is there a way to programmatically hide blank rows in this sheet starting at a specific row of the page.
View 4 Replies View RelatedI have a macro in which i can enter the rows i want to hide.
If i want to hide "position 32" i have to enter the number 8 of the row. This works fine. But now if i want to hide the "position 32" from Sheet1 it also should hide the rows 4-8 from Sheet2 [Data with 32].
Or if i hide "position 34" in Sheet1 [row 10] it also should hide the rows 14-18 in Sheet2.
Sub Button294_Click()
If Sheet1.Range("A34:A94") = "HIDE" Then
For Each cell In Range("A27:A94")
If UCase(cell.Value) = "HIDE" Then
cell.EntireRow.Hidden = True
End If
End Sub
I have a simple spreadsheet to track my mileage. It has a column calculating miles driven since the last fill-up. The "miles driven" formula is simply =B4-B3 (current odometer reading minus previous odometer reading.)
Until I input my current odometer reading, the following row's "miles driven" column displays the previous odometer reading as negative value. Example:
ROW 2: Current Odometer Reading = 53,000
ROW 3: Current Odometer Reading = 53,200; Miles Driven = 200
ROW 4: Current Odometer Reading = blank (awaiting input); Miles Driven displays as -53,200
ROW 5: Current Odometer Reading = blank (awaiting input); Miles Driven = blank
How do I hide that negative value & just display a blank cell?
I know how to keep other types of error values from displaying (zero values, #DIV0!, etc.)how to hide this single pesky negative.
I need some VBA code to hide columns in spreadsheet when run. Basically, in row 7, I've entered HIDE in the columns that I want to hide when the macro runs (i.e. columns F,J,H,I,K, O,P,R).
View 7 Replies View Relatedhow to automatically delete (or hide) columns in a spreadsheet given a list of columns that I want to retain (or see).
These are the columns I want to retain (or see):
Name
1st Phone Number
2nd Phone
Country
Conditions
Email Address
Enrollment Status
Room not available
Roommate
Mailing address
Payment Record
Payment Status
Gender
Requested room type
Total Payments to Date
What is your meal preference?
The attached has two tabs......columns I want to retain (or see) and the raw data of all the columns.
I am making a database system that tracks student activity in my schools lab for people taking foreign language courses. I'm trying to make it so students must use the userform to manage they're checking in and out, and not be able to have any direct access to the Spreadsheet itself. Also, it would be nice if the whole Spreadsheet was hidden, invisible, and only the user forms could be seen.
View 4 Replies View RelatedUsing excel's text to speech I've put together a basic spreadsheet.
[URL]
Code:
Function talkit(Speech)
Application.Speech.speak (Speech)
talkit = Speech
[Code]....
I have a worksheet used for inventory. In Column A is the quantity (to be entered manually). In Column B is the product description. In Column C is the price of the product, and Column D the total price (column C price x the quantity entered in Column A). At the bottom of the worksheet is a grand total. Also, Column B (products) is grouped into subheadings by the supplier each product came from (for example, row 6 has the title PPG, and then rows 7-137 list every product from PPG).
The calculations in this worksheet work fine. What I am trying to do is, using a macro once all of the appropriate quantities are entered in column A, automatically hide every row of product that does not have a quantity. The tricky part is, if no products under a given supplier subheader are entered, the subheader also hides, and if a quantity is entered, that subheader shows. For example, if I have no quantities under any products for PPG, then the PPG subheader hides, but if just one quantity is added, PPG shows. Also, this list will be constantly updated, new products will be put in and taken out all of the time, so I cannot base the macro on a specific number of rows.
I was wondering if it is possible to hide a column(s) but still have data in that column be visible at the bottom of the spreadsheet. For example, if I hide column G can it only hide the column from rows 1 - 50 and then the column will appear after row 50?
View 3 Replies View RelatedI have a worksheet that has a quantity column "A" and if there is no value in it I would like to hide all rows without values so only rows with quantities remain. Based on the code I have supplied below you will notice I have ranges of cells that I want to work with. The code I have works well with the exception it takes a long time maybe 30-45 seconds to perform the operation. I was wondering if there is a faster way to perform the function. I have saved it as a macro which I tied to a button.
[Code] .....
I have sheet 1 and 2 in a work book. Sheet 1 is a list of data consisting of names and address, and quantity, type, price, etc of items shipped to them. Sheet 2 takes the data from sheet one and computes certain formulas. I have allotted up to 100 rows of data to automatically compute, however I rarely need that much. Basically it goes like this. When I open the spreadsheet sheet 1 is not actually blank, instead in cell A1 I have the phrase "Customer Name", in cell B1 I have the phrase "Account Number" and so one. I do this so my coworkers know which info must go in which cells. As long as the info is put in the correct spot than cell A1 in sheet 2 with do X and B1 will do Y and so on. so if I put in 10 customers with accompanying data then the first 10 rows of sheet 2 will compute formulas and the other 90 rows will simple state "No Data" in the cells. What I would like to do is create a macro or assign a command button to HIDE all the rows that say "No Data" in column A. So if I have 30 customers entered then when I go to Sheet 2 all I have to do is hit a button and rows 31-100 become HIDDEN, not deleted.
View 3 Replies View RelatedI preferrably want to hide all rows from row 58 and down, but then unhide some rows. The rows that I want to unhide is from row 1000. How many rows that I want to unhide, depends on the content of that list. If the list is empty, then I want to unhide rows 1000-1003.
What I have written (but doesn't work), is:
Code:
Range("B58:AG1048576").Select
Selection.EntireRow.Hidden = True
If Range("C1002") = Empty Then
Erstatningsgrunnlag = Range("B1000:AG1003")
Else: Erstatningsgrunnlag = Range("C1001:AG") & Range("C1001").End(xlDown).Row + 1
End If
Erstatningsgrunnlag.EntireRow.Hidden = False
Rows 9-79 - if value in column D is zero, then hide row.
How do I do this?
Also - can this be triggered by just selecting the worksheet, or will I have to use a button?
Is there such a way/function that i could simple hide/exclude any row with ZERO value in either a pivot table or in a regalur table of data? As of now, i have to manually find the row with zero value and hide them individually.
View 9 Replies View RelatedI have a worksheet "ULIP21.xls", where in cell C10, the value can be either "Yes" or "No". If the value is "No", I want the rows 31 to rows 82 hidden in the sheet "INPUT" of the worksheet.
View 9 Replies View RelatedI want to make a macro that will recognize a value in a cell and then hide
rows that I don't need.
For example: cell is Y2
If I put into the cell the value of "abs" i whant to hide rows from below that do not contains(the rows are Y5 :Y25) "abs"
i've run a quick search on the forum and have picked up a few ideas but wonder if someone can help write some code?
I need rows 10:14 to hide in sheet2 if cell b13 in sheet 1 is blank?
I'm creating a roster which effectively needs 5 discrete pieces of information per cell - one main piece and 4 notes.
The creator of the current worksheet used comments for this function. They can't be printed, searched etc, you can't work with them and because the info can't be added with data validation it's inconsistent. What a nightmare.
I've rejected the 3d option across sheets because of the complexity and limitations of 3d calculations.
My solution is to have the main info in row 1 and the other 4 items in rows 2-5 and then repeat downwards so there are 5 rows per day.
My question is this - is there an easy way for users to hide and unhide the 4 extra rows? I need to improve readability but the extra info needs to be quickly and easily accessed by some users. Everyone else just needs to see what they are doing each day. This roster is a year long downwards so manual hiding and unhiding rows isn't practical.
e.g.:
1camerasounddirectetc>>>2hide details monwho is actually workingmarytomdick3who should have beenmungomidge4why the changesicknessholiday5type of coverovertimefreelance6details of changedouble time invoice no 10247hide details tuewho is actually workingharrymungodick8who should have beenmarymidge9why the changeswapped shiftsholiday10type of coverstafffreelance11details of change40080 invoice no 102412etc13 /
and when hidden, this:
1camerasounddirectetc>>>2show detailsmonwho is actually workingmarytomdick3show detailstuewho is actually workingharrymungodicketc /
I run a report everyday that I have to sort many times and remove unwanted rows before its down to the data that I need. What I want to try and accomplish is to say that IF column B or C contains WORD1 or WORD2 to hide that ROW that its in. Also if Column D is greater than 400 to hide that row. Better yet delete it! Below would be an example of raw data, then below it what I would only want to show:
Advisory
NIGO
IGO
127.6712/07/2009 10:1612/07/2009 12:23Retail
IGO
IGO
117.412/07/2009 10:2712/07/2009 12:24Advisory
IGO
PENDING
125.0712/07/2009 10:2312/07/2009 12:28Retail
IGO
IGO
422.6512/07/2009 10:3112/07/2009 12:34Advisory
IGO
IGO
82.5712/07/2009 11:2512/07/2009 12:47
Worksheet would just show:
Retail
IGO
IGO
117.412/07/2009 10:2712/07/2009 12:24Advisory
IGO
IGO
82.5712/07/2009 11:2512/07/2009 12:47
I have a workbook with a number of worksheets that come in twice a week. On each sheet the layout is identical as shown below with the data for each group of cities starting at row 4 for 4 rows and then continuing for a few hundred rows without a break. The city in A4 and every 4th row is merged with the 3 rows below
Unfortunately the cities do tend to change so a recorded macro would not be reliable.
A............................. B
Birmingham.....line title
merged.......... line title
merged.......... line title
merged.......... line title
Glasgow........ line title
merged................ line title
merged................ line title
merged............... line title
What I would like is to list the cities that I am interested in in the code and for it then to hide all of the other blocks of 4 rows for cities not listed.
So using the above example if I Birmingham was not listed in the code then its 4 lines would be hidden and only Glasgow would be visiable
I have got a spreadsheet with many rows of data. One row is product name and the other is date written. Now I want to calculate how long these entries have been on the log. So lets say I have 10 entries called Pension all with different dates and 10 entries called ISA with different dates. So first I need it to look for all the proucts called "Pension" and then to work out how many are 0-3 weeks old and so on. So i want it to look like this but a formula to work it out for me and to update it automatically.
0-3 Weeks 4-6 Weeks 7-9 Weeks 10 Weeks +
Pension 4 3 3 1
ISA 3 4 1 2
(this does not display very well here but I hope you understand what I mean)
I am using Excel 2003 and I dont think it as a WEEKS function so I will do it in days and then devide by 7.
I know that to look for the product i use
=COUNTIF(H:H,"Pension")
The H:H is because the product is on colum H on my spreadsheet
I know to find the date it is
=TODAY()
So to work out something 3 weeks old it would be
=TODAY()-21
I have a spreadsheet that contains traffic count data for a two lane road. The data is by direction, by hour, 24 hours a day, 365 days a year so the data is basically 2X 365 = 730 rows of data.
I need to simply add the two directions together for each hour of each day, basically add two rows together, drop down to the next two rows and add them together, repeat.
I need the result as stand alone data on another spreadsheet so the Data, Subtotals operation provided by Excel won’t work for me but something similar that puts the data on another spreadsheet would work.
I also tried to use a formula in the new spreadsheet that added two cells in two rows on the original spreadsheet together and then repeated that process a few times and then tried to drag the formula down but couldn’t get it to repeat correctly.
I've got spreadsheet I use to control the inventory of my user equipment; who has it, what is it, where is it, etc.
I'd like to "lock" multiple consecutive cells of each row so that they never seperate. This is because they need to be together. However, periodically, I need to move, sort, or rearrange the sheet.
Is there a way I can "idiot proof" this so that these cells never come unlocked from each other?
There is no vba involved (primarily because I don't know how), and no other scripting either.
I have approx 150 rows on my spreadsheet, but I am having difficulty viewing the bottom rows. The slider bar on the right hand side actually disappears on the bottom of the page. I am using windows 8. changed the tool bars and that worked, but I want to see the tool bars.
View 2 Replies View RelatedI have a spreadsheet of 300 rows of data. I need to insert a new row in between each row of data. Example insert a new blank row into rows 1,3,5 ect. Is there a way i can insert multiple rows into every other row without having to do each one separately.
View 1 Replies View RelatedI highlighted a selection of rows and clicked HIDE, now I want to unhide certain rows and when I highlight the rows above and below and right click - unhide nothing happens. I need to Unhide to find something.
View 2 Replies View RelatedHow do I select all the Columns and Rows that are not being used so that I can hide them. I like the blue background it gives when they're all hidden. I am using Excel 2007 and it's not too bad selecting all the columns but there are just way too many rows.
View 3 Replies View RelatedIs there a way in 2007 to automatically hide rows that don't contain data? (I don't want to delete them, just hide them).
View 3 Replies View Related