Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Automatically Hide Blank Rows In Spreadsheet


I 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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Hide Blank Rows Based On Values In Other Rows
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.

View Replies!   View Related
Delete Off All Blank Rows In A Spreadsheet
How can I delete all blank rows in a spreadsheet without sorting the data as I want it to be in the exact order it is in.

View Replies!   View Related
Automatically Add Rows To End Of Spreadsheet By Pushing Enter
I am making a template for my company to automatically calculate the amount of sheet metal needed for a specific job. The spreadsheet could get very long depending on how much duct is needed. Is there any way I can automatically insert rows to the end of the sheet by pressing enter after filling the last row with data, which would then move the totals down. Also, the formatting of the rows I wish to add need to be copies of the ones above.

View Replies!   View Related
Hide Blank Rows ..
How do I go about writing VBA code to do the following:

1. Select a particular range of cells in column A

2. If one of these cells (in column A) returns a FALSE response to the Excel IsNonText formula to hide its entire row.

The circumstance is a little difficult to explain but let me know if you need clarification and I'll do my best.

View Replies!   View Related
Hide Blank Rows Within A Range...
I have this code (compliments of VBA Noob) which hides all blank rows within a range ("Range1") P16:V650. It works great in a new worksheet with little amount data, however within my heavy worksheet, it takes over a minute to compile.

View Replies!   View Related
Hide Blank Rows Within A Range
Can't seem to find a solution searching through various forums searches. I would like to hide all blank rows within a Named Range : Range1 (A1:E8).............

View Replies!   View Related
Hide Show Blank Rows
I have a lengthy column containing text information. Within this column are various gaps. ie: several cells with no information.

eg:

..... Row F

1...text info
2...text info
3
4
5...text info
6...text info
7
8...text info

I would like to create a simple macro (switched via toggle switch) whereby it hides/unhides the rows containing cells with no text information.

It should also be mentioned that this column contains various background color formatting, for both empty and text cells.

View Replies!   View Related
Automatically Hide Rows Based On A Value In Column Q
After the user selects something using the validation list in cell F38, the macro should check in column Q in the rows 44 till 425 for "hide". For each row in which it finds "hide" the entire row should be hidden.

I have the code below but it does't work yet. It calculates for ages and afterwards only the first 2 rows of the 381 are hidden (probably because the third row is not "hide").

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Dim ThisCell As Range
Target = Range("F38")

Set MyRange = Range("Q44:Q" & Range("Q425").End(xlUp).Row)
For Each ThisCell In MyRange
If ThisCell.Value = "hide" Then
ThisCell.EntireRow.Hidden = True
End If
Next ThisCell
End Sub

View Replies!   View Related
Automatically Hide/Show Rows Across Worksheets
When I call a series of subroutines from different worksheets, ScreenUpdating = False is not working.Here is my

Private Sub Worksheet_Change(ByVal Target As Range)
With Application
.Calculation = xlManual
.EnableEvents = False
End With
If ActiveCell = "No" Then
Call Sheet3.DisableTBs
Call Sheet4.DisableTBs
Call Sheet5.DisableTBs
Call Sheet6.DisableTBs
Call Sheet8.DisableTBs
Call Sheet9.DisableTBs
Call Sheet10.DisableTBs......................

View Replies!   View Related
Hide Blank Rows - Non Text/color
I received this code which hides blank rows within a range.

View Replies!   View Related
Hide Blank Rows In Non Contiguous Range
How do you select certain ranges in the same column to filter? The ranges I need are "c36:c50" & "c54:c68" & "c72:c87" & "c91:c155" & "c158:c172" & "c176:c202" all filtering for blanks. I can not filter from c36:c202 because there are blanks in the missing rows and they have to stay.

View Replies!   View Related
Hide Blank Rows Based On Value In Column
What I have is a sheet where the cells in range A9:A3508 have a formula that evaluates to blank if any of a variety of conditions are not met (date falls outside desired range, does not meet filter criteria, etc.) and a number if these conditions are met. What I am looking for is for all rows in that range where A is blank to be hidden (not deleted), and for this to automatically update every time A changes (meaning that if A goes from blank to a number, that I will need that row to become unhidden again). I have considered just recording the macro and calling the function with a button, but as this is for external users, some of whom may be unfamiliar with Excel, I would rather keep it as clean, simple, and automatic as possible.

View Replies!   View Related
Hide Blank Rows Which Spans Two Columns
Hide Multiple rows. I have text within two columns A, D. How can I Hide Blank rows which spans two column Named Ranges, "Range1" (A1:A15) "Range2" (D1:D15). As an example:

Beginning with this:

...A......D

1.Text
2........Text
3.
4.
5.
6.Text
7.Text
8.
9.
10.......Text
11.Text
12.
13.......Text
14.
15.......Text

To this , after hiding blanks:
...A......D

1.Text
2........Text
6.Text
7.Text
10.......Text
11.Text
13.......Text
15.......Text

View Replies!   View Related
Macro To Automatically Delete Data And Hide Rows
I have a spreadsheet that allows room for 35 students per period, but instead of having the teacher manually go in and delete the extra data and hide the rows, I want to create a macro that will do so.

In cell B4 the teacher will enter how many students are in their first period class. (cell C4 for second period, cell D4 for third etc...) I want the entering of the number to automatically hide the superfluous rows and delete the data in the second column for those rows. I don't want the rows to be completely deleted because another year they may have more students and need those rows back.

I have attached one of the workbooks that I need to put this macro into. I have created room for 35 students in a given period. So if they have 23 students entered into B4 (period one) I would need rows 30-41 to be hidden, and I would need the formulas in B30:B41, E30:E41, H30:H31, K30:K31 etc....deleted.

Basically the point of the formulas is the teacher will enter the total points possible on that given assignment in cell B6, E6, H6 etc....and it autofills that score down, so the teacher only enters those that missed points instead of entering in the missed ones and the 100 percent ones.

I would need those formulas to be deleted because if the assignment was out of 10 then cell B30-B41 will give students a 10, and then the class average will be computing those scores,but those students don't exist.

But if I can't get the macro to auto delete the formulas, I will just not have those formulas in there, and the teacher can enter all scores.

View Replies!   View Related
Automatically Hide / Show Rows Based On Cell Value
I wrote a small code to hide some columns if a certain cell is equal to a certain string.

The cell is actually a drop down list and when they select a certain one, I want it to hide 2 columns. So I wrote the code with sub name Action, but I want it to be running all the time. I tried to achieve this by writing the following code however it gave me error 438 for my 2nd line.

Sub Auto_Open()
Range("A1").OnEntry = "Action"
End Sub

Auto Merged Post Until 24 Hrs Passes;Oh, by the way error 438 states: Object doesn't support this property or method

View Replies!   View Related
Automatically Hide & Show Rows Based On COUNTIF Result
What i would like to do is Hide Rows Based on Cell Value in Multiple Sheets & Multiple Columns
and i need the macro to be fast

Sheet1
If the value in Range BE11:BE160 equals 1 don’t hide the row
If the value is “0” or “ “ then hide the row
Sheet2
If the value in Range BE11:BE160 equals 1 don’t hide the row
if the value is “0” or “ “ then hide the row
Sheet3
If the value in Range BE11:BE160 equals 1 don’t hide the row
if the value is “0” or “ “ then hide the row
Sheet4
If the value in Range O1:O150 equals 1 don’t hide the row
if the value is “0” or “ “ then hide the row
Sheet4
If the value in Range B1:B150 equals 1 don’t hide the row
if the value is “0” or “ “ then hide the row

View Replies!   View Related
Macro Code Automatically To Hide Rows Based Condition
I have a spreadsheet that calculates percentages and then outputs the results to a pie chart. There are 9 different percentages being graphed in cells A41 to A49. The chart looks weird if any of the percentages end up being 0, so I have the formula set to add 0.00001 to each calculation (so they show up as 0% and display on the chart as 0%, but truly are 0.00001). I would like it so that if any of these 9 percentages ends up being 0 (or really 0.00001) that the row automatically hides and thus won't display on the pie chart. How can I create a macro that automatically runs to accomplish this, and automatically updates as percentages are recalculated.

View Replies!   View Related
Count The # Of Rows In A Spreadsheet In Which There Is Non-blank Text Data In 2 Separate Columns
I am trying to count the # of rows in a spreadsheet in which there is non-blank text data in 2 separate columns. For instance, if the spreadsheet looked like the one below (dashes just for formatting purposes):

NAMES-------THIS-------------THAT
Mary-------some text--------some more text
John-------<blanks>---------just text here
Sue--------just some here-----<blanks>
Dave-------something--------something else
Adam------<blanks>-----------<blanks>

The total # of rows with something in both the "THIS" and "THAT" columns above would therefore be 2.

View Replies!   View Related
VBA Macro To Filter Rows By Cell Value & Hide Blank Columns
i have created a spreadsheet to simplify our work flow, I am stuck on what is probably the easiest of the commands.

basically have rows dedicated to specific codes and the colums represent values relating to each code, all codes have a different set of values, the attached example only has a few variables but the actual worksheet will have several hundred.

the idea is the user will input the code they wish to get details on in A2 and then press the command button and it will then show (as per the after sheet in the attachment) just the relevant information for that code, so filter the code in column A and hide the columns which hold no value.

where i am getting stuck is I am not sure the best way to proceed, is it best to create the macro button to do the filter and hide or is there a better way using vlookup and a pop up window asking for the relevantcode to be inputted to to retrive the information, again understand there will be hundreds of colums and hundreds of rows and the values may be 20 or 30 colums apart for some of the Codes so this simplification is really saving the user a lot of time.

View Replies!   View Related
Hide Blank/Empty Rows & Shown/Unhide Non Empty Ones
I am getting values for my excel sheet from another department excel sheet . everything works fine. If there is no values in the rows in the Department sheet, then i need to hide the rows in my sheet. How to code this in VBA. When they add values to the rows then i should make the rows visble here. Kindy give me a sample of vba code to this or suggest me to solve.

View Replies!   View Related
Password Protect/hide The Spreadsheet, Not Forms
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 Replies!   View Related
Updating One Spreadsheet Off Of Another Automatically
I am trying to update 25 different spreadsheets, and have the latest row of data from each of the 25 automatically go to a 26th spreadsheet that is my summary.

My goal is anytime I add a new row of data in any of the 25, that the summary sheet gets updated automatically.

View Replies!   View Related
Count Blank Cells Within A Range Not Including Fully Blank Rows
I can count the blank cells withiin a range using

=COUNTBLANK(C6:AD2506)

But I dont want it to count the cells if the entire row, within that cell, i.e. C6:AD6, is blank.

It should only count the blank cells within a row if there has been some data entered on that row..provided it has been entered within the specified range.

View Replies!   View Related
Write A Letter Automatically From Spreadsheet
I have a master spreadsheet that I hold data relating to an event, the spreadsheet lists various companies names and contact details that I will need to the event and also what function they will provide by yes/no boxes. the data is currently spread all over the spreadsheet but I suppose it could be linked into some sort of simple table.

I then have to email or telex all the companies and request their assistance. Can excel automate this task? I know it can write an individual letter but could it write one message to all requesting exactly what I need that I could cut and paster to the application I would use to transmit the request?

Also when the list of Companies ends can the message move onto the next part.ie if there was a list of 4 Companies to use the message would start "Attn: Co1 / Co2 / Co3 / Co4" but if there was only 2 I would want "Attn: Co1 / Co2" only I currently get "Attn: Co1 / Co2 / /" For example

if the "catering" column and "water" column has "yes" but the "marquee" column has "no" as a check could it write "Please provide catering and water" BUT if all three columns were "yes" could it make it "Please provide catering, water and marquee" ?

View Replies!   View Related
Automatically Hide X Columns
I am trying to let Excel automatically hide unneeded columns. A brief description:
In this sheet one should enter a number of "projects" into cell DW7 (7,127). The maximum number of projects is 60. Each project consists of 2 columns (with already prepared formats and functions). The projects are numbered from 1 to 60 (the actual project number is in Cell G6 (6,7), I6 (6,9)...).
For this purpose I wrote the following

Sub worksheet_calculate() 'Hide columns that are not needed
Application. ScreenUpdating = False
Dim i As Integer
Dim r As Integer
i = 1
For r = 6 To 126
If Cells(6, r + i) > Cells(7, 127) Then
Columns(r).EntireColumn.Hidden = True
End If

If Cells(6, r + i) <= Cells(7, 127) Then
Columns(r).EntireColumn.Hidden = False
End If..................

View Replies!   View Related
Code To Automatically Add In A Time And Date To Spreadsheet
Need the code which would allow me to create a macro where when data is added into an excel sheet, it automatically adds in the date and the time?

View Replies!   View Related
Hide & Show Sheets Automatically
I would like to be able to use the before save event to hide some sheets before the save then after unhide some sheets. So that the user carrys on with the sheets they had before saving but when the document is reopened the correct sheets are hidden.

This is what I have so far but unfortunately when you click close and then save changes it runs the before save code and then goes around in circles, reasking the user if they want to save changes

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet

Cancel = True
Application.EnableEvents = False
Sheets("Protected Content").Visible = True
For Each ws In Worksheets
If ws. Name <> "Protected Content" Then ws.Visible = False
Next ws
Me.Save
For Each ws In Worksheets
ws.Visible = True
Next ws
Sheets("Protected Content").Visible = False
Application.EnableEvents = True

End Sub

View Replies!   View Related
Formula: Spreadsheet To Automatically Update The Latest Results
I've adapted a spreadsheet to automatically update the latest results.

But my formula (D11), that I got from here, has now stopped working.

I suspect this is due to the formula I've used columns H and I. But I have no solution.

Also the very similar formula used in M15 to M44 has also stopped working, probably for the same reason.

View Replies!   View Related
Socecr Spreadsheet: Sort Automatically By Gathered Points
I’ve got a soccer spreadsheet with the names of six national teams. Near it, the small table that present current condition of a team. Under it, I created the small table where I want them to be sorted automatically by gathered points. The spreadsheet. For example:...............

I want the name of the team, who gathered maximum points, to be shown in first row. In the second row, must be shown the team which has the second result. There must be one formula that I can copy down. So I wrote the next formula
=MATCH(LARGE(A1:A9;ROWS($A$1:A1));A1:A9;0)

Everything was perfect until I saw that if the column has the same numbers (points) it returns the first matched row number. So I did the next

IF(COUNTIF($AD$4:$AD$9,LARGE($AD$4:$AD$9,ROWS($AD$4:AD4)))>1,MATCH(LARGE($Z$4:$Z$9,B12),$Z$4:$Z$9,0) +3,MATCH(LARGE($AD$4:$AD$9,B12),$AD$4:$AD$9,0)+3)..................

View Replies!   View Related
Macro That Automatically Saves A Backup Of Spreadsheet Every Week
Is there a macro that automatically saves a backup of your spreadsheet every week?

View Replies!   View Related
Booking Userform: The Spreadsheet To Automatically Save Under C:My Documents
I have put together a booking spreadsheet which uses a UserForm. see attached worksheet. When you start the UserForm and select a name, date, number of nights, and then 'Add', the data is added to the worksheet.

1. My first problems is that when I select the 'Cancel' button on the Userform, I would like the spreadsheet to automatically save under C:My Documents. And also save the file as 'Marketing_DDMMYYYY.xls' as todays date.

2. My second problem is that the 'Leaving Data' column sometimes shows the leaving date in American format (MM/DD/YYYY), and sometimes English format (DD/MM/YYYY).
How can I make this always use English format?

View Replies!   View Related
Hide Row If ALL Cells In Rng Are Blank
I found this in my search, offered by the brilliant Roy UK:

This will hide all rows that have blank cells in C or D ...

View Replies!   View Related
Hide Two Columns If One Is Blank
Currently using this code to search Column D and hide if the cells are blank. How would I go about also hiding Column C as well, If D is blank?

I am using the spreadsheet as a grading template. The assignment name is under column C and the grade under column D. If there is no grade then I would like to be able to hide the unused assignment column along with the empty grade column.

Sub HideColumnsInd()
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Dim rCell As Range
For Each rCell In Range("D3:D48")
If rCell = "" Then
rCell.EntireColumn.Hidden = True
Else
rCell.EntireColumn.Hidden = False
End If
Next rCell
Application.EnableEvents = True
End Sub


View Replies!   View Related
IF/VLOOKUP (set Up A Spreadsheet Which Automatically Calculates The Start And End Dates Of Project Tasks)
I am trying to set up a spreadsheet which automatically calculates the start and end dates of project tasks, by looking at the order in which tasks need to be completed. I have attached a spreadsheet to show what I am trying to do.

View Replies!   View Related
Hide Blank Cell Pivot Table
I'm using pivot table and i want to erase the blank cell that come out with pivot table and replace it with number 0 or -, how can i do that ? i've be try to do this by go to the table option and in the empty cells check list i've put 0 or - but the pivot table still come out "blank".

View Replies!   View Related
Hide Columns If Cell In Specific Row Is Blank
I have a row in a table from columns C to CZ. All the cells in the row contain a formula, between 1 and 204 cells in the row will have a value (i.e. will not be blank).

The cells with a value will start at column C and may or may not have a blank cell before no more values and blank cells to the end (Col GZ).

Example:

C D E F G H I J K >..........................GZ

23 34 67 74 2 34 6 2 56 all blank ("") to end

or:

C D E F G H I J K >..........................GZ

23 34 67 "" "" 34 6 all blank ("") to end

I need to hide the entire columns when the cells in this row are blank but NOT if the blank cell has valued cells after it (i.e. do not hide columns F and G in the second example.

I can do this by looping back from col GZ and hiding the columns one at a time, which is very slow. I am stuck on the code to select all the relavent columns and hide together.

View Replies!   View Related
Automatically Add Blank Columns
I import a spreadsheet each month that summarizes expenses. The columns are the months: Col B=Jan, Col C=Feb, etc. I need to add a blank column between each month. Next month the import will have: Jan, Feb, Mar. Then the next will have Jan, Feb, Mar, Apr. macro that would add the blank column between months. Since the spreadsheet columns are growing each month the macro would have to be able to count how many columns to insert.

View Replies!   View Related
Automatically Hide/Unhide Columns Based On Cell Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 13 Then Exit Sub
If InStr(Target.Value, "Other (specify in next column)") Then
Columns("N").Hidden = False
ElseIf WorksheetFunction. CountIf(Columns("M"), "Other (specify in next column)") = 0 Then
Columns("N").Hidden = True
End If
End Sub

but I have a lot of columns that I need to perform as above and I have put the code together as below

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next

If Target.Column = 13 And InStr(Target.Value, "Other (specify in next column)") Then
Columns("N").Hidden = False
ElseIf WorksheetFunction.CountIf(Columns("M"), "Other (specify in next column)") = 0 Then.................

Using the above code, when I selected more than one cell anywhere in my workbook and pressed delete I was bugging out with a runtime error 13 message. You can see from the above code that I inserted "On Error Resume Next" - this got rid of the runtime error 13 message, but now when I select more than one cell and press delete, hidden columns are incorrectly revealed in my worksheet. how I can extend the working code at the top of this posting so that it works for a number of different columns in my Worksheet i.e. without the runtime error 13 occurring and without columns being incorrectly revealed.

View Replies!   View Related
Add Blank Cell Automatically In A Column
For example, In excel in cells A2 to A56 I've got words in them, I wanted to copy all the cells in column A to B in same order,but when the word "check" is encountered, a blank cell will be added below the cell with the word "check". How can I do this?

View Replies!   View Related
How To Automatically Copy Data From Any Blank Cell Above?
I receive a huge spreadsheet with account numbers in col A and names in col b with dates in col g and amounts in h. However if there is more than one date and amount for the same account number and name, the people/program who originated the data left those fields blank.

In order to sort the list by name by date, I have been manually copying the data from the row immediately above to the blank row(s). (L O N G process) So if I have ...

View Replies!   View Related
Delete Blank Rows & Rows Below Meeting Condition
I have an imported report in a spreadsheet. It imports to three columns. I need to check each row in column A for three seperate criteria and delete the rows I don't need. I need to delete blank rows and check next row for page header info. Delete these and next rows to next blank cell. Check next row for page header and not delete if not page header. Several rows down will be a cell with 23 blank spaces before the word Reg: and sometimes other words past this but always this first. This row is to be kept. I looked at the FAQ's example of Deleting but I don't think it will work. I also need to put a key word in column A at a point where I want to stop. This report is a couple thousand rows long so a VBA procedure would really save time. I have a procedure I use to check for two zero's in two cells that hide these rows but I couldn't modify it to work on this report.

View Replies!   View Related
Delete Blank Rows (formula Not Deleting All Rows)
I have the following codes to delete all blank rows in column A

Dim lastrow As Long
lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
MsgBox lastrow

With Sheet1
For t = 1 To lastrow
If Cells(t, 1) = "" Then
Rows(t).Delete
End If
Next t
End With

End Sub

Although it is working , it is not deleting all the blank rows at once, I have to keep pressing on the macro button running the macro several times, until all blank rows are completely deleted.

View Replies!   View Related
Eliminate Blank Rows By Shifting Rows Up
I have the code below that clears colums B-J and L-N in whatever row you activate a cell in. When a row gets cleared, how do I shift all the other rows up to eliminate blank rows in between the data real-time? I've deactivated the cut function already since this messes up references in the worksheet, so it would have to only use copy, paste, and clearcontents functions. Also, I don't want to DELETE any rows, just essentially shift the blank rows to the bottom. So when a row is cleared, all others shift up and the blank one goes to the end of the data that is available for data entry, so rows 17 to 116 are always available. But it must check to make sure that B-J and L-N are ALL blank, otherwise some wanted rows with one piece of information might get cleared. So at any given time, my range will always go from row 17 to 116. This is Excel 2007. Here is the code I have to clear rows that can be built upon.

View Replies!   View Related
Counting Rows And Inserting Blank Rows
I have an excel sheet that has the A column populated with many different things.

I'm not sure how possible it is, but I'm trying to group everything up by 7s and putting a space in between the groups.

So...

ABCDEFG1
ABCDEFG2
ABCDEFG3
ABCDEFG4
ABCDEFG5
ABCDEFG6
ABCDEFG7
HIJKLMNOP1
HIJKLMNOP2
HIJKLMNOP3
HIJKLMNOP4
HIJKLMNOP5
HIJKLMNOP6
HIJKLMNOP7
QRSTUV1
QRSTUV2
QRSTUV3
QRSTUV4
QRSTUV5
QRSTUV6
QRSTUV7

Would turn into

ABCDEFG1
ABCDEFG2
ABCDEFG3
ABCDEFG4
ABCDEFG5
ABCDEFG6
ABCDEFG7

HIJKLMNOP1
HIJKLMNOP2
HIJKLMNOP3
HIJKLMNOP4
HIJKLMNOP5
HIJKLMNOP6
HIJKLMNOP7

QRSTUV1
QRSTUV2
QRSTUV3
QRSTUV4
QRSTUV5
QRSTUV6
QRSTUV7

View Replies!   View Related
Spreadsheet With Many Rows Of Data
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

View Replies!   View Related
Add Two Spreadsheet Rows And Repeat
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.

View Replies!   View Related
Hide All Rows With ZERO Value
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 Replies!   View Related
VBA For Hide Rows
I 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"


View Replies!   View Related
Hide The Rows
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

View Replies!   View Related
How To Hide Rows
I 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 Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved