Add number of used rows to footer?

Is it possible to add the number of used rows to a spreadsheet's
footer? I have a basic list in Excel 2000. the number of rows in the
list changes weekly, and I would like to find a simple way to:

1 - Count the number of rows in the current list (minus the header
row)

2 - Display this number in the footer of the printed spreadsheet.

If anyone can help with either of these, I'd appreciate it!

Thanks,
E
0
atwork1 (5)
8/20/2003 11:29:02 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
416 Views

Similar Articles

[PageSpeed] 23

If there are no embedded blank cells in column A, you can get a count of the filled rows (minus
the header) with the formula =COUNTA(A:A) - 1

To put that number in a footer, you need to use the event macro, Workbook.Before_Print

Private Sub Workbook_BeforePrint(Cancel As Boolean)
  With Worksheets("Sheet1")
    .PageSetup.RightFooter = Application.CountA(.Columns(1)) - 1
  End With
End Sub

That code goes in the module named ThisWorkbook. Change the sheet name and column to check, as
needed.


On Wed, 20 Aug 2003 07:29:02 -0400, Elwood Dowd <atwork@dea.spamcon.org> wrote:

>Is it possible to add the number of used rows to a spreadsheet's
>footer? I have a basic list in Excel 2000. the number of rows in the
>list changes weekly, and I would like to find a simple way to:
>
>1 - Count the number of rows in the current list (minus the header
>row)
>
>2 - Display this number in the footer of the printed spreadsheet.
>
>If anyone can help with either of these, I'd appreciate it!
>
>Thanks,
>E

0
myrnalarson (223)
8/20/2003 5:43:47 PM
Reply:

Similar Artilces:

multiple users using single email address
Hi, I am new to Exchange, so forgive the possible silly question, but I cannot find the answer to my requirement. What I want to do is have a single email address which is picked up by several people, but these people can also reply and send to received emails. I am unsure of the best way to configure exchange and then the Outlook 2003 client, to allow this to take place without having to resort to 'Sending on behalf of', which is OK but not perfect. Am using SBS 2003 with Exchange and Outlook 2003. Many thanks in advance for any help. Mark Mark, You can sent up a Distri...

How to count rows with changing data
I have an imported list on sheet2 and it is maybe 100 rows. Each day the data is imported the dates change along with the type of record associated with each date. Say this week there are 25 rows with 11/13, 20 rows with 11/14 and 25 rows with 11/15 and 30 rows with 11/16. Mixed in with this each of these dates might have a different type of record (each type has 4 options.) I need to be able to do the following: 1.) I need to count the number of occurrences for each date and not only show the total count but also show the date that is counted as the label. 2.)count the...

How to use Like and Or in Form.Filter VB Statement
This Works: Forms![Master Search].Form.Filter = "[GTPO] = Forms![Master Search]! Text63" FilterOn = True This Doesn't: Forms![Master Search].Form.Filter = "[GTPO] = Forms![Master Search]! Text63" Or "[Project Name] Like '*" & Forms![Master Search]!Text64 & "*'" FilterOn = True what is the correct syntax How many times are you going to ask the same question? I've already answered you: did that not work for you? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) <himmelrich@gmail.com...

How do you match one column's numbers with the 2nd column?
How do you math one column's numbers with the 2nd column? Like if you had: 1 2 2 3 3 6 4 5 6 7 ... and you wanted it sorted like so 2 2 3 3 6 6 1 4 5 7 Any idea how to do that? Thanks, Lee:confused -- lnsykalsk ----------------------------------------------------------------------- lnsykalski's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3403 View this thread: http://www.excelforum.com/showthread.php?threadid=53792 Assuming your data are in columns A and B insert a staging column before column B, with following formu...

How to add MFC Support to ATL Service in Visual C++.NET
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C4A62F.EF5DEB80 Content-Type: text/plain; charset="windows-1256" Content-Transfer-Encoding: quoted-printable I=B4m trying to add MFC support to a ATL Service in VC7 but it doesn=B4t = seem to be the same way you do it in VC6, anyone knows howto? Regards ------=_NextPart_000_0008_01C4A62F.EF5DEB80 Content-Type: text/html; charset="windows-1256" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <ME...

Pivot table add one column
How can I add one column in pivot table? When I added a column of Average 100, pivot table created 3 more columns for the first 3 columns( and I don't like it) My data Book Label Price Average A Hard paper 200 100 B Soft 100 100 C Hard paper 200 100 F Plastic 50 100 K Soft 100 100 L Plastic 50 100 M Hard paper 200 100 K soft 100 100 R Soft 100 100 T Plastic 50 100 ------------------------------------------------------------------------------------------- My pivot ...

BIN2DEC conversion for large binary numbers
Hello All, I need your help, i want to convert large binary numbers using excel but whenever i use the BIN2DEC function the result is negative which is not correct. Ex. BIN2DEC(1100110110) the result is -202 but if i use the calculator the result is 822 which is the correct value Any ideas? what shall i do to have the correct value? Thanks in advance A.M. Hi, Well I think it's 822 and my calculator confirms that and like you I get -202. Someone will no doubt explain why but in the meantime use this =SUMPRODUCT(MID("0"&A1,ROW(INDIRECT("1:"&a...

Need 2 rows to display X axis data points for a line graph
X axis is 100 data points, and all must be displayed. They do display, but are all mushed up. In Corel, you have the option to display the X axis on up to 3 rows, with interspersed tick marks. In Excel, I seem to be able only to manipulate the number of categories between tick marks and tick mark labels, but can't stagger data onto multiple rows. Any ideas or add-ins I could try? Excel 2002. Thanks, Susan This web page gives a suggestion for staggering your labels: http://peltiertech.com/Excel/Charts/Staggered.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technic...

X and Y axis
How do I create a graph that uses X, Y and Z axes? Hi, Excel does not, as standard, do 3 value dimension charts. Have a read of Tushar's page on 3d surface charts. http://tushar-mehta.com/excel/charts/3d_surface/index.html You can fake a 3d plot using formula. Check out Fernando Cinquegrani's example. http://www.prodomosua.it/ppage02.html Or my own version. http://www.andypope.info/charts/3drotate.htm Cheers Andy SS wrote: > How do I create a graph that uses X, Y and Z axes? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

formatting numbers in excel 2003
In Excel 2000 I could set my format to number, 2 decimals, place comma, and fixed decimals would be checked in the tools, options. When I enter 329 and press enter I would get 3.29 in cell When I enter 329. and press enter I would get 329.00 in cell. I cannot make this work in 2003 If I have it set as describted above I get 3.29 in either case. Please tell me what to do. 20 years of entering this way will not change overnight if I cannot find the problem. Any help is greatly appreciated. tammie This appears to be an inadvertant change in XL2003. See this thread from a google sear...

Use extract into a condition
I have a sumproduct formula with different conditions. One of them is picking up only the first 17 caracters of a cell. So LEFT(H10,17) =SUMPRODUCT(--(Details!$C$4:$C$151=Summary!G10),--(Details!$D$4:$D$151= LEFT(H10,17) ),--(Details!$E$4:$E$151=I10),Details!$F$4:$F$151) How do I put it on the formula? Thanks in advance Mosqui > =SUMPRODUCT(--(Details!$C$4:$C$151=Summary!G10),--(Details!$D$4:$D$151= > LEFT(H10,17) ),--(Details!$E$4:$E$151=I10),Details!$F$4:$F$151) Not sure what you're asking. Your formula is syntactically correct if yo...

Clearing recently used list?
Hi, I created a test money file just so I can play around with it. I deleted the file now but it still shows up in the recently used list under the File menu. How do I go about removing it? TIA. In microsoft.public.money, KcN wrote: > >I created a test money file just so I can play around with it. I deleted >the file now but it still shows up in the recently used list under the File >menu. How do I go about removing it? You would have to search it out in the registry. What am I looking for? I searched for "test" since that's what I named my file... also ...

what formula do i use to figure payroll in my spredsheet
=Hours * Rate Or post some details in the large white space as I have done. Gord Dibben MS Excel MVP On Thu, 15 Oct 2009 09:29:48 -0700, excel 101 <excel 101@discussions.microsoft.com> wrote: ...

Appending the rows of several tabs in xls sheets?
Is it possible to append all the rows of several tabs into one sheet? We are getting inventories in excel files organized in several tabs: 1st Tab: instructions for the users 2nd Tab: information about the country (currency, tax rate, etc) 3rd Tab: inventory of one site within the country ....to... All tabs have the same format (5 rows header, then identical Nth Tab: rows containing data about one item per row. The name of each tab 3 to N is the site name (company code). N is of course different for each country, and the number of lines in each site inventory is also variable (but they a...

Sending e-mail Newsletters using Access Database
What is the best way to e-mail a newsletter to customers whose e-mail addresses are located in an Access Database? Thanks, -- Mike Schumann "Mike Schumann" <mike-nospam@traditions-nospam.com> wrote in message news:OeSAXmneKHA.6096@TK2MSFTNGP02.phx.gbl... > What is the best way to e-mail a newsletter to customers whose e-mail > addresses are located in an Access Database? How about: http://www.datastrat.com/Code/MultipleEmail.txt -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com Bom ...

Autofill formulas when inserting rows in excel tables
Is it possible to have the formula in a column automatically be placed in a cell when you add a new row into an excel table (not a data table). Seems I saw this somewhere. ...

Seperate text and number to different cells
Hi, I have some data in cell (A1) which is alpha numeric, from this data I want number and text in seperate cells (B1) and (C1) respectively per the below example. A B C Raw Data Numbers Text asho344555k123 344555123 ashok 123ab47 12347 ab 1affu123 1123 affu Thanks Afroz The following User Defined Function will return the numerals in a mixed cell: Public Function ReturnNumerals(rng As Range) As String Dim sStr As String, i As Long, ...

Is it a way to use GetScrollInfo for a browser window
Hello, I want to add the autoscroll feature to my screen capture program, and for that purpose need to know the current position of the captured window having its handle only. (I use GetScrollInfo to read the position)... but I got a problem with IE window - I see no way to get the scrollbar handle (looks like the GetScrollInfo function will work for such window with SB_CTL option only) Does anyone know a solution on how to obtain the IE scrollbar's handle or how to get the current position? Thanks in advance, Sergey Pozhilov http://www.wisepixel.com ...

How do I find the bugs in MS Reader Add-In
I am trying to create my Personal e-Book in the MS Reader Add-in, First attempt Worked OK, 2nd attempt was to add TOC and now it failes every time, with attempting to change or modify. The Error Pop-up is; "Unable to build the eBook (error 66: Unrecognized CSS directive: '@list', This content would fail on the Pocket PC version of the Reader, and must not appera in files compatible with that version.)." Any one have any ideas?? or Information on the MS Reader Add-In on Word 2007?? Thanks Larry ...

How do i compare numbers in cells with formula
I have a report I am attempting to automate as much as possible. What I have done is put in the following: Cell J is a current % Cell K is my product type (a number 19,20) Cell L is =if(k2=19,"65%",if(k2=20,"75%")) Cell M I want it to tell me whether the % in cell J is more then L, when I input the formula =if(j2>=L2,"Yes","No") it does not change but if I delete the formula in L and type a % in it works....HELP Ditch the double quotees around the percentages =if(k2=19,65%,if(k2=20,75%)) "nolechik" wrote: > I have a report I am a...

how do I put a "&" in the footer?
I'm working on Excel 2003 but the rest of my co-workers are on older versions of Excel. I put an image of our logo as a footer on all our templates, but because this option is not available on prior versions, it only shows up on my documents. So instead of using the image, I just typed it in, but my company name has an ampersand in it..... which is a command function (i.e., "&P" = page number) is there a way to use the "&" in the footer??? Thanks in advance! Use two (&&) ampersands Greg "W Chang" <W Chang@discussions.microsoft.com...

Checked boxes won't clear out when I add a new form
I created a simple Access database for entering names and addresses etc. for a rock climbing club and created checkboxes for 4 types of rock climbing activities (Lead Climbing, Belaying, etc.) that must be checked if applicable as part of a liability/assumption legal requirement. The problem is that if I check one or two of the boxes, then create a new record, the checked boxes on the previous record also become part of the new record. What are the properties that I should set so that a new record is blank? PS - I'm not a technical person - and it's a very simple database so a ...

Numbers and letters...
Hi. I have in column A the numbers 1- 26, in column B the letters A- Z. I'm trying to create a formula wherein, when I type a number 1- 26 in C, the corresponding letter will appear in D (if I type '1', 'A' will appear, if I type '2', 'B' will appear, and so on). I was nesting a VLOOKUP in an IF function but I'm having problems. I suspect I'm making this more complicated than need be. I'd appreciate some assistance. silas On Nov 9, 12:09 pm, "silas" <silas.NOSPAMprop...@gmail.com> wrote: > Hi. I have in column A the n...

Extract multiple numbers from a cell
Here are some examples of text with multiple numbers embedded: A1 = 3031 // 2841;1886-ring road location A2 = 3305 //1455-historical A3 = //3491;3492 A4 = //inactive location; historical = 1790; enter new locaion Result should be: B1 = 3031;2841;1886 B2 = 3305;1455 B3 = 3491;3492 B4 = 1790 Try this UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. =ExtractNumbers(A1) Function ExtractNumbers(strData As String) As String Dim intTemp As Intege...

Wrapping doesn't collapse rows
I have a column that allows wrapping. But if I widen the column so that all the text is on one line or less lines that they were, the rows don't collapse to the smaller size. I have to manually go to each row and make them smaller by hand. Is there a way to tell Excel to make the rows only as large as the text after I widen the column? It does that if I shorten the width - it makes the rows larger, to the size of each row. Thanks, Tom I use xl2003 and I don't see the rowheight adjust when I widen or reduce the columnwidth. You could use a macro that adjusts the rows (all...