VB list box print reports. Known # of columns. # rows unknown

I have a Listbox with a number of reports. I know the number of columns in 
the report but not the number of rows in the report. This will vary based on 
data. How do I set the print area in VB when I don't know the number of rows.
0
Utf
3/4/2010 3:26:01 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
670 Views

Similar Articles

[PageSpeed] 33

One way, assuming your data begins in cell A1.

Set rng = ActiveSheet.Range(Cells(1, 1), _
                Cells(UsedRange.Rows.Count, UsedRange.ColumnsCount))
ActiveSheet.PageSetUp.PrintArea = rng



"Richard" <Richard@discussions.microsoft.com> wrote in message 
news:A660B32E-BEB0-4EFF-A146-0943407D4F91@microsoft.com...
>I have a Listbox with a number of reports. I know the number of columns in
> the report but not the number of rows in the report. This will vary based 
> on
> data. How do I set the print area in VB when I don't know the number of 
> rows. 


0
JLGWhiz
3/4/2010 4:07:07 PM
  P.S.  Unless you have page breaks assigned in your worksheet, Excel will 
normally query the printer interface and determine how to paginate the used 
range.  It will then print the entire printable area without the user having 
to set a print range.  You only have to be concerned if there is data 
somewhere in a remote area of the worksheet that would cause unneeded blank 
pages to be printed.


"JLGWhiz" <JLGWhiz@cfl.rr.com> wrote in message 
news:e7yaIU7uKHA.5316@TK2MSFTNGP05.phx.gbl...
> One way, assuming your data begins in cell A1.
>
> Set rng = ActiveSheet.Range(Cells(1, 1), _
>                Cells(UsedRange.Rows.Count, UsedRange.ColumnsCount))
> ActiveSheet.PageSetUp.PrintArea = rng
>
>
>
> "Richard" <Richard@discussions.microsoft.com> wrote in message 
> news:A660B32E-BEB0-4EFF-A146-0943407D4F91@microsoft.com...
>>I have a Listbox with a number of reports. I know the number of columns in
>> the report but not the number of rows in the report. This will vary based 
>> on
>> data. How do I set the print area in VB when I don't know the number of 
>> rows.
>
> 


0
JLGWhiz
3/4/2010 6:11:13 PM
Reply:

Similar Artilces:

Why Range name not showing in list
I have been studying creating range names in VBA. I have two procedures that work (shown below.) When the first example is run, the range name does not display in the toolbars Name Box but the second one does. I don't understand why and if I create the name using the first method, how can I get it to show up in the Name box list? 1st Example ActiveWorkbook.Names.Add Name:="ListTbl", _ RefersTo:="=Offset(Sheet2!$A:$A,0,0,COUNTA(Sheet!$A:$A),4)" 2nd Example ‘ Sheets("ListTbl").Activate ' ' With ActiveWorkbook...

How can I do one-time printing without set page breaks?
I have a Excell document with set page breaks. Sometimes I sort differently and want to print that without the page breaks. How can I do that without having to remove my previous page breaks and then inserting them again? Thanks! hi, you can't. but you can record print macros for each way you wish to print including changing headers and footers. you can do each on macro record. this way you don't have to re-set everything back up. the print macro will do that for you. all you do is click macro run. just a suggestion. i use this technique. regards FSt1 "kewlblondboy&q...

Adding to a list
How would you go about adding to a list from another worksheet? -- famdamly ------------------------------------------------------------------------ famdamly's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29382 View this thread: http://www.excelforum.com/showthread.php?threadid=492635 More information please. Rgds, ScottO "famdamly" <famdamly.1zwvva_1134364800.9494@excelforum-nospam.com> wrote in message news:famdamly.1zwvva_1134364800.9494@excelforum-nospam.com... | | How would you go about adding to a list from another worksheet? | | | --...

Deleting a Row based on cell value
Friends My work sheet contains 5000 rows I have to delete entire row when ever Column A contain data "NA" How to automate this? Thanks in advance Babu ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Babu, Use a macro Private Sub DeleteNA() Dim x As Integer For x = 5000 To 1 Step -1 If Sheets("Sheet1").Range("A" & x).Value = "NA" Then Rows(x).EntireRow.Delete End If Next x End Sub HTH Henry "Babu" <Bab...

Export distribution list into excel
Hi , guys: I'm working with Outlook 2007, I need to export a distribution list containing user's names and email address into an excel sheet. If I select all the rows and copy past, it only pasts the title of the distribution list. Can I get more details for the DL? or there is any other method to export the DL to excel , could anyone help me ? thanks. DL's do not support export and import. The best thing to do is save the DL as a text file then open it in Excel as a tab or comma delimited file. -- Russ Valentine "Eric Wang" <Eric Wang@discussi...

VB distmon?
Hi I have recently installed Office 2003. A couple of wired things happen in Excel: > Everytime I close the program I get a MS VB error that reads "Compile error in hidden module: Distmon" Every time open the program I get the same error, but the macro it is calling is "autoexecnew" > Since my new installation, I type in an simple function like TRIM and it tells me cannot find project library. any ideas? Thnaks! Alan Alan http://support.microsoft.com/default.aspx?scid=kb;EN-US;q307410 OFF: "Compile Error in Hidden Module" Error Message When You Sta...

Creating an Email to send to a list from excel
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: pop I have a email list in excel that has about 4,000 email address in it. I want to create a single email that I can send to all the people on this list. I do not need the email bondy to be personalized, so don't need to use the mail merge function through Word if not necessary. Basically I want to create an email blast and use my own list from excel. What is the best way to go about this? Thanks Hi, simply copy the e-mail addresses in Excel and paste them into the To field of an Entou...

Sales by vendor report #2
What's the easiest way to get a Sales by Vendor report (with item detail)? Is this report coming from SOP? I don't understand what you need. "Elaine" wrote: > What's the easiest way to get a Sales by Vendor report (with item detail)? Do you need this information to calculate discounts passed on to customers from vendors? Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com "Elaine" wrote: > What's the easiest way t...

Excel VB
I have two cells in a workbook that are dates. The cell Start_Date has a valid date in it. The second cell Test_Date is either blank or has a valid date in it. The IsDate test on both cells works fine, however, on a blank cell the Month function extracts "12". Anyone know why? I had to put the month function within a test for IsDate to make it work. Seems like Month function on a blank cell should return something other than a 12. Dave Test code: If IsDate(Range("Start_Date")) Then 'Cell has 11/01/09 in it. MsgBox ("start date a d...

Combo or List box
I'm struggling with list or combo boxes and would appreciate some help or maybe I'm looking for the wrong thing. I have a form in which I enter several codes to create the criteria for a report query. This works just fine. There is an associated description for each code in other tables. All I want to do is to display alongside the input box the associated description of the code I've just entered. Thanks On Wed, 18 Apr 2007 11:00:02 -0700, SeeJay <SeeJay@discussions.microsoft.com> wrote: >All I want to do is to display alongside the input box the associated &...

how do I add a list of numbers to get a total
If your numbers are in cells A1 through A10, try: =SUM(A1:A10) If you have something more complex in mind, please post an example. tj "bibs" wrote: > Hi! If your list is in the range A1:A25: =SUM(A1:A25) Biff ...

Printing "continued" after last line on a page
On a student transcript, I'm trying to find a way, for multi-page transcripts, to print a line with "continued" at the end of details. The student's page is grouped by semesters and the detail lines for the semester are kept together on the page. Therefore the continued line (which is there to prevent extra courses from being typed in) cannot be at the top of the page footer. I want to print it at the bottom of the semester footer and just make it visible when I know this is the last semester group on the page. Is there any way I can know this? The WillContinue proper...

Can't print envelopes in Word X or Word 2004
Working on document recently, changed page setup to be 14 x 8-1/2. Word changed normal.dot without prompting me at SAVE (which is my default), after that all documents were 14 x 8-1/2, changing it didn't help. Finally deleted normal.dot, Word then created a new one with 8-1/2 x 11 page setup, the default. However, ever since I changed page setup to 14x8-1/2, I have been unable to print envelopes -- using Envelope Wizard, I insert text, click PRINT, the envelope displays properly on screen -- but print preview shows a blank envelope. And a blank envelope is printed. I upgraded to Office...

Cannot Add any window to VB
Hello: I'm using a VM with GP 10.0 SP3 installed. In order to have the same as our production databases i copy the GP folder, entirely, which i think it was a mistake now I'm getting a "This cannot be added to Visual Basic", whenever i try to add a window, any window, to VB. Also it goes without saying that there's a lot of load errors starting GP. For the record the copy/paste thing worked, sort of, and before you start thinking that i was insane to have done this, there's a reason behind it, There's been a lot of tweaks to our production server, by our p...

Money 2001 hanging when trying to customise reports
This problem has started recently. I can't open Favourite reports and I can't customise what does open. When I exit using ALT-CTRL-DEL I get a message saying program is waiting for an input from me. The problem also occurs in a totally new file. I have tried level 1 and Level 2 fixes - no problems reported. I have also tried changing system date (as per MS item suggesting a fix) but the problem also occurs even at the very first entry date. In microsoft.public.money, Elmar wrote: >This problem has started recently. I can't open Favourite reports and I >can...

Macro: make list of all files in all folder with sub folders
Hey guys You have kindly helped me before, and I hope for a little bit more My macro (below) makes a filename-list of all FILES in one certain (SUB)FOLDER, but I need to redesign it to make a list of ALL FILES in FOLDER + ALL FILES in every SUBFOLDER (on every sublevel) in this Folder-structure. I think you guys know the trick - will you share it with me? Best regards Snoopy My macro goes like this: (How can I modify it?) Sub ListAllFilesInFolderWithDir() ' Delete existing list in sheet FILES/column B to prepare for new input On Error Resume Next Sheets("...

ZZ report error
I have a customer who does a Z report at the end of each shift and a ZZ report at the end of the day after his final Z report. When he runs the ZZ report it prints out the next Z report with all zeros. He is 100% sure that he is choosing the ZZ report option, but it seams to just print the next Z report which is all zeros since he just zeroed out. I have yet to see this in person but did see a copy of his report. Is there anything that would not allow him to print a ZZ report and just default to a Z report? Very strange. Thanks when you run a ZZ report it automatically prints a Z...

Modify the master category list
Does anyone know if you can / how to Modify the default master category list so when you click "default" it will reset the list to 'your desired list'? CompuRollin wrote: > Does anyone know if you can / how to Modify the default master > category list so when you click "default" it will reset the list to > 'your desired list'? This isn't part of Exchange -the categories list is part of Outlook. See if http://www.slipstick.com/outlook/olcat.htm helps - best to post Outlook questions to m.p.outlook ...

When sending email to multiple users, some recipients do not show up in the To or CC box
Good Morning All I have come across a really weird problem that only happens to one user. They are using Outlook 2000. When they send an email to multiple people, not all of them show up as the email being sent to them in the To or CC box after the email has been sent. All recipients do receive the email but obviously when some of them open it they cannot see the whole list of who the email was sent to as they dont seem to appear on the list. This happens more often if the email is sent to external email addresses rather than the ones within my company but it has happened with internal addre...

Combining 2 lists
How do I combine 2 Excel lists, or databases, with similar fields, into one list? "stevenrhonda" wrote: > How do I combine 2 Excel lists, or databases, > with similar fields, into one list? VLOOKUP would be one way .. Try Debra Dalgleish's nice coverage at her page: http://www.contextures.com/xlFunctions02.html (there's a sample book for d/l there) Another way would be to use INDEX / MATCH which Debra also covers at her: http://www.contextures.com/xlFunctions03.html (a sample book for d/l is available there as well) -- Max Singapore http://savefile.com/projects/236...

Q : Message box detached
Hello, is it possible to make a message box that is not modal (OK only) juste to diplay information without having a human return action ? I tried : int code =MessageBoxEx( NULL, // handle to owner window "message", // text in message box "title", // message box title MB_ICONINFORMATION || MB_OK || MB_SERVICE_NOTIFICATION, // message box style 0 // language identifier ); but the result is always a OK / Cancel message box ! Any idea ? Thanks, Cyrille Proust "Cyrille Proust" wrote: > Hello, > > is it possible to make a messa...

More Problems with List Box
Hi Having some problems running a report with a list box selection. Below is the code I am using. The code should create a criteria (in the field Seperator) to run a report based upon a query, however this is not happening. If I enter the criteria directly into the query and then run the query it works fine, so it would appear the criteria is not being passed to the query. The problem is the report runs and shows all records from the table regardless of the selection in the list box Private Sub cmdRunReport_Click() Dim varItem As Variant Dim strCriteria As String Dim strAss...

Spam and Black Lists
Ok, I understand what a Black List is but somehow my server has been put on one. I am in the process of trying to determine which list I am on but have not had much luck. What I want to find out is if there is any central point for checking a blacklist. I googled Blacklist and I get too many hits to try them all. I am currently working at finding out from the organizations we are sending mail to in order to find out which lists they use but thought I would ask here. Also, In the NDR I noticed that it is listing the Servername.DomainName.Local name instead of the mail.EmailDomainNam...

picture under transparent object doesn't print
I have a picture on my brochure. I also, for effect, addect a square over the picture in which I added color, then gradient, and then made it transparent (18%). I can see the picture under the square on the screen, but in both print preview and when I actually print it, there is only the square with no picture under it. What am I doing wrong or is it the program? See if there's a newer printer driver. -- Don Vancouver, USA "rlcoburn" <rlcoburn@discussions.microsoft.com> wrote in message news:DEBB7EA1-4DEF-4FF3-87EF-81D066B1BF14@microsoft.com... >I have a pi...

deleting rows from one sheet based on 2nd sheet
I need help - please: I have 2 Excel sheets: 1 – Contains all contact details (aaprx 900 rows) 2- Contains email addresses which have bounced back over the past 3 months (approx 80 rows) Sheet 1 - first_name, last_name, email, company, job_title, street_no, etc, etc, etc., Sheet 2 – email_address How can I write a macro or function to delete the rows in sheet 1 that contain the bounced email addresses listed in sheet 2 All help appreciated. Here's a quick fix you could apply using formulas .. Source data is assumed in Sheet1, cols A to H (say), data from row2 down, with ema...