Macro To Print Active Area Excluding Cells With Forumula That Doesn't Return Value
Trying to put together a macro that looks down active sheet for all cells that contain a value, sets a print area and then prints !
Is this possible?
FYG, I have a column that run from 3 - 2000, which contains a formula, which may produce a value depending on corresponding cells.
I used this code from a post on a similar topic, but excel is complaining code
in bold
Private Sub Print_Area_Click()
Dim lastCell As Range
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0)
Do Until Application.Count(lastCell.EntireRow) 0
Set lastCell = lastCell.Offset(-1, 0)
Loop
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address
End Sub
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Return True If Value Exist. False If It Doesn't
I have two price lists in workbook. One containing "normal" price list, other containing "action" prices. I want to search for a product name code from column A in "normal" price list in column A of "action" list, and if it is found to show it somehow in any column in "normal" price list sheet. This way I know that there is action price attached to that product and that I have to search for the price in "action" sheet.
View Replies!
View Related
Print Area With A Macro
I am looking to set the print area with a macro, below is what I had hoped would work. It does not. I get unable to get Vlookup property of worksheetFunction class error. Sub Printzed() Start = Range("C6").Address Finish = Res = Application.WorksheetFunction.VLookup(I5, Range("prlu"), 2).Address myPrintArea = Start & ":" & Finish ActiveSheet.PageSetup.PrintArea = myPrintArea End Sub
View Replies!
View Related
Set Print Area To Used Cells
I am trying to write a macro that will allow the user to print out the sheet from the beginning to the last used row. The catch is that I have formats and formulas all the way to the end (A1:O319). My basic format is a frozen Pane (for a header and summary) from A1:O18, then the user inputs their data starting on B19. The columns never change, and the rows will be variable. I tried a dynamic range to no avail, and have most recently tried this Sub printter() Dim myrange Dim Rng, cell As Range Set myrange = Range("A320").End(xlUp) Set Rng = Range("A1:" & myrange.Address) For Each cell In Rng If cell = "" Then myrange = cell.Address MsgBox cell.Address ActiveSheet.PageSetup.PrintArea = "$A$1:O" & cell.Row ActiveSheet.PrintOut Exit Sub End If Next End Sub My next plan would be to write to check to see the last used cell in column B, since that cell has only formating, no formula, and is a required entry cell
View Replies!
View Related
Using Macro To Set Print Area
My macro selects cells containing data - then i want the macro to set the print area to those selected cells - works fine - but the selected cells won't be the same each time i run the macro. Here's how it looks now: Range("A1:M1").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select ActiveSheet.PageSetup.PrintArea = "$A$1:$M$76" The next time i run the macro the selected cells won't be A1:M76 - how do i have the macro set the print area to whatever is selected?
View Replies!
View Related
Non-contiguous Print Area Macro
I have a document which will require signatures after printed and want to keep the signature portion at the bottom of the page (currently rows 102-104). The problem is that the document will not need to be that long for every person and was wondering if it would be possible to have a macro that checks (C7:C:100), stops printing when it reaches the blank cells, and then prints those last 2 rows?
View Replies!
View Related
Adjust Print Area Macro
The macro below works perfectly except for one problem. I only want it to set the print area from Column A - Column M . It currently selects data i have in Columns N and beyond and i do not need that to print. From A - M is 13 columns.
View Replies!
View Related
Limit Print Area To Cells With Values
In the range A1:H89 I have expanding and retracting values - all of which is determined by the numbers in column A. Column A is simply A1+1 copied down. The value of A1 is determined by the value of another cell, based on age. If the value in column A exceeds a specific figure (let's say 50), formulas in columns B - H result in answers of "" (this is "nothing"?). Assume that the value of 50 is reached in A20. All cells from A21 (inclusive) down, including B21 - H21, will have the values "" in them and show up as nothing on my screen. When I print, I'd like to only print those cells where data is, i.e. cells A1:H20. Sub Drukwerk() With ActiveSheet.PageSetup .PrintTitleRows = "$7:$9" .PrintTitleColumns = "" End With LastRow = Range("A" & Rows.Count).End(xlUp).Row ActiveSheet.PageSetup.PrintArea = "A1:H" & LastRow ' ActiveSheet.PageSetup.PrintArea = "$A$1:$H$89" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "".......................
View Replies!
View Related
Macro To Select An Area, And Print Selection
I need a macro to select the area within the blue invoice sheet box, so everything within the blue invoice sheet box is selected , and then the selection should be printed. I will have hundreds of these invoice slips made, right below each other, i was wondering once this is done, is there an easier way to to have each invoice selected, instead of making a custom button for each sheet ( which selects just that invoice , and prints that selection ). ( Column m through v, starting at row 2 stoping at row 68, is the selection required in this example, everythign within the blue box. ).
View Replies!
View Related
User Defined Print Area Macro
I am looking to create a macro that will ask the user to define the row number of the beginning and ending locations that they would like to print. The columns are fixed in that the first starting row will be from column A and the ending row will be column M. I have built a pipe tally used in the oilfield that keeps track of large amounts of pipe figures but very often is the case that there is the need to print a section of the tally data in reference to a certain depth. With the vast knowledge base located here I was able to build the first macro that prints the whole active tally but now I need something more refined. Below is one of the copies of the macro that I was trying to get to work and it is missing a couple of things but the most important part shows up and that is how to get the inputbox variables into the defined print area. Sub TallyVariable() Dim StartRow As String Dim EndRow As String StartRow = InputBox("Please Enter Starting Row you would like to print") EndRow = InputBox("Please Enter Last Row you would like to print") ActiveSheet.PageSetup.PrintArea = "A" & StartRow.Address:"M" & EndRow.Address End Sub
View Replies!
View Related
Macro To Set Print Area W/o Hidden Rows
We run a small gardening shop and use a simple spreadsheet to track of various things. Row A contains headers, with the data following in the rows underneath. This table is now quite large, and we therefore hide 600 or so rows so that only rows with data from the last week is shown. We often need to print this for easy reference. I currently do this by highlighting the area I want and setting it as print area. The print therefore doesn't include hidden cells, which is what I need. The problem we have, therefore, is that we have to manually select this print area each time. My experience with macros is VERY limited, to the point where I can record one which will select the print area and print. However, as the list gets longer and more rows are hidden the range obviously needs to change, and my simple macro will not keep up. So my question is as follows. Is there a simple macro I could write to assign to a button that could "keep up" with the moving range? So either it always prints the header row and the, for example, 50 rows beneath (exlcuding the hidden ones), or, even better, it prints the header row and all rows with data in them that are relevant to the last week. Each row has a cell for the date it concerns so assume this may be possible?
View Replies!
View Related
Setting Variable, Discontinuous Print Area In A Macro
I need to write a macro to set the print area to the first two columns (A & B), and the last 12 columns (the last column may change). In both cases I need to print all rows (start is row 1, last row is variable). I am trying to achieve something like the "Freeze Pane" effect with the printer. The first two rows contain column headers (dates). The first two columns contain information that needs to be included on the printout, whilst the last 12 columns contain the most recent data. Various cells withing the selected ranges may be blank, but no row or column will be entirely blank.
View Replies!
View Related
Min Formula Excluding Any 0's Or Empty Cells
I have tried a number of different ones from my search of this forum but I'm getting an error. here is a sample of my spreadsheet. I want it to give me the lowest number, I don't want it to look at the 0's or if the cell is blank. Here is my formula and my error: =SMALL(N196:N203,COUNTIF($N$196:$N$203,0)+1) my error is #num! I've also tried others including using the MIN function but either 0 is entered or the error above. 3402414
View Replies!
View Related
Macro Doesn't Work On Button, But Fine From Macro Menu!
I've recorded a macro that copies an entire tab into a new spreadsheet then goes on the copy and paste information from one tab to another. When I run the macro from the Tools>Macros menu it works perfectly. But when I copy the code and add it to that of a button it fails and posts the following error: Run-time error '1001': Select method of Range class failed. The first attachement shows the code for the macro as it is alone, and the second shows how I simply copied and pasted it into the 'view code' window of the button. Needless to say I'm a beginner at macros and only every record them, I can usually make stuff work that way but this has me stumped!
View Replies!
View Related
Return Active Cell's Location/row
I'm having trouble identifing a way to return a location for the position of the active cell. I've searched Excel help with "Position, location, return, activecell, etc." and I can't seem to figure this out. I know that it's possible, so that's why I'm on here! ... Ok, say the active cell is currently "F1", and I need the location "F1" to identify the ROW to be used in a formula later, how would I go about that? The current contents of cell "F1"' will be "REPLACE", but I need to change the words "REPLACE" in "F1" and other cells labeled "REPLACE" in column F to the following formula (where the "1" in "A1" is is the current row):
View Replies!
View Related
Print Previewed A Worksheet And Find A Tiny Image With A Small Portion Of The Print Area
I just converted from Excel 2003 to 2007 and print previewed a worksheet and find a tiny image with a small portion of the print area. When I look at the sheet in page break preview mode, I see the 8.5 x 11 sheet broken up into approx 77 smaller sheets. When I try to move the page breaks to include the whole sheet it states the change cannot be made as it will result in an image less than 10%. I have attached two images - the first shows the print preview I get, and the second show the multiple pages when I view in page break.
View Replies!
View Related
Script Works In Debugger But Doesn't When Macro Runs
I am trying to do a very simple Macro for merging data. My problem is that the macro works when I am stepping through in debugger, but does not work when I am in excel and I use the shortcut key (crtl + m) to run the macro. What happens when it doesn't work properly is it selects rows 2 and 3 and tries to paste them to the new workbook and I get an error saying the cells are not the correct type. I don't want it to do this because rows 2 and 3 are titles and have nothing to do with the data I am moving. As I said before, when I open VBE and step through the code it works just fine. The error only happens when there is no data on the initial work sheet.
View Replies!
View Related
Insert Picture Macro Doesn't Work In 2007
I just upgraded to Excel 2007 and my macro from Excel 2003 for inserting a picture doesn't work correctly in 2007. I have a command button that states insert picture and when you click it, it will let you insert a picture into the cell and hide the command button. In 2007 it will let you insert the picture, but it isn't centered and expands into other cells and the command button is still visible. The picture appears to be the correct size it just doesn't center itself in the cell and the command button is still visible. Below is the
View Replies!
View Related
Recorded Macro Doesn't Seem To Provide Expected Result
On column A, I simply do an A-Z sort that gives the expected result. So I recorded it as a macro. In column G is a code that corresponds with column A. When the marcro is executed Column A is sorted but the corresponding value in G stays where it is. Clicking the A-Z button works. But when that action is recorded as a macro I get the above quirck. Since VBA is not my beef, I don't really know what wrong.
View Replies!
View Related
Macro Doesn't Work When Workbook/File Is Shared
I am encountering a specific scenerio where In I am creating a New file by copying one of the sheets And renaming that With todays date. here starts me problem when I try To share the sheet To work around I am seeing that th macro Is Not copying the sheet properly even though i have given PasteSpecial. The code goes here VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing Is Not transferred when copy/pasting, but Is If the keyword uses "quotes". Sub Newsheet() sheetname = Format(Now, "dd-mmm-yyyy") MsgBox sheetname sheet_count = Worksheets.Count 'Checking for Replication................
View Replies!
View Related
Return Column Header Of First Column That Doesn't Contain 0
I'm looking for a formula that will return the column header (a date) of the first instance of a number greater than 0. For example if columns A through S had dates as in row 1 (header row), and in row 2, every column contained a 0 up until column P, I would want the date (Row 1, Column P) returned? I think this might be a sort of index/match formula, but I am not very familiar with these.
View Replies!
View Related
Combo Box Macro In Worksheet Doesn't Run Automatically
I have a combo box in a worksheet with a macro written for it that works fine but when I open the worksheet it doesn't run automatically, I have to find the macro and tell it to run. How can I get it to run as soon as the worksheet is opened Here is the code for the macro Private Sub combobox() ComboBox1.clear ComboBox1.AddItem "Mit Rekuperator" ComboBox1.AddItem "Ohne Rekuperator" End Sub Private Sub ComboBox1_Change() Run "PinchInternHeatExchanger" Select Case ComboBox1.Text Case "Mit Rekuperator" ActiveSheet.Range("I62") = Worksheets("uorc").Range("L99") Case "Ohne Rekuperator" ActiveSheet.Range("I62") = Worksheets("uorc").Range("E11") End Select Run "PinchPoint" End Sub
View Replies!
View Related
Import Macro Trying To Gain Acces To File That Doesn't Exist.
I get the following error: "Run-time error '1004': "cmc4906.xls" File cannot be found. Check the spelling of the file name and verify that the file location is correct." I am not trying to open a xls file. The path is clear and there are no file names with extensions in the path name (C:Weekly). I'm unclear of why the code thinks its looking for file cmc4906.xls and a xls file at that. Sub Import() Dim inputfile As Variant Dim path As Variant path = ("C:Weeklys") inputfile = Dir("C:Weeklys") Do While inputfile <> "" Workbooks.OpenText Filename:=inputfile, Origin:=437, StartRow _ :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, Comma:=False, _ Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 1), Array( _ 3, 1), Array(4, 1), Array(5, 2), Array(6, 2), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _.............
View Replies!
View Related
Bug - Macro Crashes When Run, Doesn't Crash If Save First
I'm just wondering if any of you can lend some help on a bug i'm encountering. When I press a button on my worksheet to call a macro that brings up a userform, the excel will crash, and give that "do you want to report this error", close down excel, and reopen on a blank worksheet. Strange thing is, this error is avoided if I save the workbook upon opening it, and then call that macro. The workbook does not read data from external sources, so i dont think is that problem. Has anyone encountered this?
View Replies!
View Related
Vb Code To Set Print Area And Print Out
with creating vb code that will select certain rows in a spreadsheet, set the print area to those rows and print them out, and then reset the print area back to default. The data is all on one sheet, but is split into 5 different areas, each below the next, with the column headers at the top of each. e.g. header_____header_____header data_______data_______data data_______data_______data data_______data_______data header_____header_____header data_______data_______data data_______data_______data data_______data_______data and so on. At the press of a button it'll set the print area to the first section and print (the section includes the headers as well as the data). Then at the press of another button it'll set the print area to the next section. There could be any amount of rows of data in each section so it needs to cope with that.
View Replies!
View Related
Why Doesn't My If Function Print An "X"
My function is Finding the inputted last name on another sheet then looking up the date at the top of the column and comparing it too the current date, However I can't get the current cell to = "X" when they do equal. Private Sub ReturningRush_Click() Dim Value7 As String Dim strCheck As Range Value7 = InputBox(prompt:="Enter LAST Name") With Sheets("Attendance") Set strCheck = .Range("B6:B200").Find(what:=Value7, LookIn:=xlValues, Lookat:=xlWhole, _SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not strCheck Is Nothing Then Application.Goto strCheck ActiveCell.Offset(0, 2).Select
View Replies!
View Related
Return To Active Cell After Macro Run
I am using Excel to tabulate scores for my employees. We work in a very busy and open office, so there is a need to be able to obfuscate the scores, but also help keep from losing my place while punching the scores. I would like it to be able to return to the last cell that I was punching a score in...I used "ActiveCell.SpecialCells(xlLastCell).Select". I have also used " x= cells(Rows.count,2).end(xlUP).row" followed by "cells(x+1,2).select", but both with no luck... Sub Hide_Scores() Range("B15:EU35").Select ActiveSheet. Unprotect With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0
View Replies!
View Related
Active Area
I have the following macro that works fine. But, I would like it to stop when it gets to the bottom of the active area. Sub Insert_for_SQs() Do Selection.EntireRow.Insert ActiveCell.Offset(6, 0).EntireRow.Activate Selection.EntireRow.Insert ActiveCell.Offset(12, 0).EntireRow.Activate Loop End Sub
View Replies!
View Related
Doesn't Work If The Worksheet Hasn't Been Saved Yet
I'm trying to simplify a formula that I'm using in a spreadsheet that's being used mostly in Excel 2000. 2 reasons I'm doing this: 1) It doesn't work if the worksheet hasn't been saved yet. 2) It's ugly =DATE(YEAR(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3) & " 20" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,2))),MONTH(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3) & " 20" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,2)))+1,0) The formula returns the last day of the month of the name of the sheet. For example, if the sheet is names "Mar 07", it returns 3/31/07. (yeah, when the 2100 issue becomes a big deal, I'm not going to be ready but I have 92 years to think about it) Even if I can't make it prettier, does anyone have a way to get the sheet name into a formula without VBA Code in a file that hasn't been saved.
View Replies!
View Related
Sort Area Around Active Cell By Two Columns
I have various lists of data (using Data > List > Create List...) that do not always have the first cell in the list in column A. I have spent quite a bit of time searching the internet to try and find a macro that will work the same as... Data > Sort > Sort By: Col xx > Then by: Col xy. Dave supplied me with a macro that will sort a range by the first column: Sub SortThis() With ActiveCell. CurrentRegion .Sort Key1:=. Cells(1, 1), Order1:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With End Sub I have recorded macro that does the same and looked at the code but this uses Range and specifies the cells to be sorted. I have not been able to work out how to modify the code Dave posted to do a minor sort by the second column. I am after a macro that will determine the range (could be a varying number of columns and rows in different places). For however many columns there may be the macro will sort the full width and height by the first column and then the second column, with all the data within a particular row not being split.
View Replies!
View Related
Print Only Used Area
I have a sheet that will be about 5 pages long if printed. To minimize wasted ink and paper, using VB I would like to be able to have it only print the portion uses (data entered) along with a section at the top and bottom. The section at the top would print on the top of the first page and the section at the bottom would print on the last page. I know I could record a macro to select an area and print that but it would not do what I am trying to accomplish.
View Replies!
View Related
Print Area Always Changes -
I use the code below to print a portion of a sheet. My problem is that the area always changes. The $A$476 should be the first cell in that column that contains "HEA" The "536" in $F$536 is the last row that contains data. There are conditional formats all the way down to row 1,000 but not data Private Sub CommandButton1_Click() Dim strCurrentPrinter As String, strNetworkPrinter As String ****strNetworkPrinter = GetFullNetworkPrinterName("Adobe PDF") ****If Len(strNetworkPrinter) > 0 Then ' found the network printer ********strCurrentPrinter = Application.ActivePrinter ********' change to the network printer ********Application.ActivePrinter = strNetworkPrinter ********ActiveSheet.PageSetup.PrintArea = "$A$476:$F$536" ********ActiveSheet.PrintOut** 'print something ********' change back to the previously active printer ********Application.ActivePrinter = strCurrentPrinter ********ActiveSheet.PageSetup.PrintArea = "" ****End If End Sub Obviously there is a function also that finds the full network printer name, I didn't think it was important, because my issue is the print area. Everything else works fine. If you need it let me know and I will post it.
View Replies!
View Related
Variable Print Area
I'm using Excel 2003. I have a spreadsheet used for costing jobs giving a detailed breakdown of parts and time. Sometimes the customer requests a copy so I'm creating a macro for my boss that prints the spreadsheet, less the hidden columns that I don't want the customer to see. This I can manage. My question is this: Is there a way within the macro to set the print area to change to where the last cell is automatically? Each spreadsheet is a different length dependent upon the work required and setting a larger print area to compensate would use excess paper and ink.
View Replies!
View Related
Print Area Dimension
I used the macro recorder for this: ActiveSheet.PageSetup.PrintArea = "$A$1:$P$246" How would I change this to use variables? I need to set the print area to A( FR ) to P( LR ) Of course FR is first row, LR is last row
View Replies!
View Related
Auto Print Area
I have a hard time transitioning from other examples to my workbook. What I am after, is for the print area on 2 sheets to be set by the count of non-empty cells in column B on the first sheet. I have a maximum number of entries of 200 (rows) plus a header row. So, my entry #200 is in worksheet row 201 (cell B201) In cell B203 I use formula "COUNTA" to tell me how many cells are not empty. As an example, this number might be 36. The print area should be limited to worksheet row 37 (1 more than 36 due to the header row). My columns are always the same, A thru I. On a second worksheet, measurements are recorded, 10 per row. So, if I have 36 items to be measured, I use 4 rows (10 per row=3 full rows and 6/10 of the 4th row). I would like to have the print areas set automatically, based on this number in cell B203 (see above notes). On the 1st sheet (using example of 36 entries), my print area needs to be thru row 37, columns A thru I. On the 2nd sheet, my print area needs to increase 10 rows every time B203 increases by 10. (this 2nd sheet has other info on it, so when the print area increases, it needs to go in steps of 10 rows. as an example, if B203=8, then the print area on the 2nd sheet includes cell A1 thru cell K13, but if B203=12, then the print area would be cell A1 thru cell K23)
View Replies!
View Related
Print Area VB Code
I wish the print area to be set based on the number entered into cell "D58". My code so far is: Sub Worksheet_SelectionChange(ByVal Target As Range) Dim NumPages As Range Set NumPages = Range("D48") Worksheets("Sheet6").PageSetup.PrintArea = Worksheets("Sheet6").Range(0, 0).Resize(NumPages * 21, 47) End Sub
View Replies!
View Related
Print Area Code
I have some code to select a print area depending on wether or not there is data in a cell. The code I have so far partially works but not quite like I need it to. Here's what I have, Sub selectprintarea() Select Case True Case Range("C5").Value > 0 Range("A1:M47").Select ActiveSheet.PageSetup.PrintArea = "$A$1:$M$47" Case Range("Q5").Value > 0 Range("A1:AA47").Select ActiveSheet.PageSetup.PrintArea = "$A$1:$AA$47" End Select End Sub It will select the print range if there is data in C5 but if there is data in C5 and Q5 it only selects the first print area. I know I need an "If - Else" or something of that nature but I cant figure that out. Also I need it to look at other worksheets not just the active one. I've tried a replacing the ActiveSheet with Worksheets (Sheet1). but that gives me an error.
View Replies!
View Related
Dynamic Print Area
I can define a name to calculate the desired Print Area for a page. For example, print_area_notes: =Notes!$A$1:INDEX(Notes!$A:$Z,final_Note,5). I can then use that name when defining the Print Area via Page Setup. The problem is it calculates the cell range immediately and substitutes that in the Print Area field. When print_area_notes changes the actual Print Area does not. Is there some formula I can enter in Print Area that will allow this value to be set dynamically? I have tried setting Print Area to =indirect("print_area_notes") but it doesn't like that. I could, but prefer not to, write a VBA function to set the Print Area but even then how do I set it to be called automatically? I'd prefer to be able to enter a formula and do it without VBA code because you then have the security issues every time the spreadsheet is opened.
View Replies!
View Related
Macro To Print Page Based On Active Page
I need a macro that will print a hidden page that is linked to the page I have active. It needs to be able to find the correct hidden page that corresponds to the active page because there are multilple pairs of the hidden/visible pages. The name of the hidden page is the same as the visible one with "printout" added to the end. When created, the sheet code names are sheetn and sheetm; n and m being consecutive numbers.
View Replies!
View Related
How To Auto Set Print Area
if there is a way with functions or code to automatically set the print area. I would like to have it set to start at A1 and print to column G & the first ROW with a zero value in column F. The ROW in which the first zero value appears will be the only variable. It will always be column G.
View Replies!
View Related
|
TRACKER
