Don't Print Shaded Cells.
I want to print the value in a shaded cell but I do not want the shaded cell itself to print
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Don't Print Hidden Rows
I have a worksheet with hidden rows and Page Breaks. I know that the page breaks are causing my worksheet to print blank pages where I have page breaks and hidden rows. I need the page breaks or the form just prints a mess... I tried eliminating them - but that didn't work at all. I need a macro, which will examine the worksheet, look for the hidden rows, exclude them from the print area, and then Print the worksheet.
View Replies!
View Related
Printing :: Don't Print A Column
I am after a code that will sort out the below printing problem I want excel to hide ALL OF column E from the printer, i want to see the information myself but when i press print, column E will not show on my printed page i have found this Private Sub Workbook_BeforePrint(Cancel As Boolean) If ActiveSheet.Name = "Sheet1" Then Cancel = True Application.EnableEvents = False Application.ScreenUpdating = False With ActiveSheet .Range("e1").EntireColumn.Hidden = True .PrintOut .Range("e1").EntireColumn.Hidden = False End With Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub but am not sure how to enter it, also if the code is correct. I have entered it using the alt+f11 then clicked on this sheet, and put it in there but it still dont work
View Replies!
View Related
Don't Copy Blank Cells
i have a small macro that is deign to move info from sheet1 to sheet2, column B in sheet one has a list of names, not every cell has a name in it, if cell B8 has a name then that cell and cellC8 is copyied to sheet2 column B&C. The macro i have copies over the blanks as well and i end up with sheet2 column B cell 8 filled cell 12 filled in etc. How do i stop the blanks? Private Sub CommandButton2_Click() 'Transfer Results ActiveSheet.Range("B14:C400").copy Worksheets("points").Select Sheets("Points").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues End Sub
View Replies!
View Related
Add If 4 Cells Don't Match
I have four columns of data that I'd like to compare. If none of the cells match, then I'd like to add all the cells together. If all the cells are different, then I'd like the number to match the others. Here's the problem: if two or three cells match, but the others are different, I'd like to take one of the matching cells and add it with the cell(s) that does not match. Examples (each number is a cell): 1 2 3 4 = 10 1 1 2 3 = 6 1 1 1 2 = 3 1 1 1 1 = 1
View Replies!
View Related
VBA: Don't Want To Graph Empty Cells
I am making a program that several people are going to be inputting data into. I selected a huge range to ge graphed, so people could simply add their data and it would atomatically appear on the chart. The problem is, that I don't know how much data I am going to get in total. I want to select a range, but only have it appear on the chart if the cells are not empty (I don't want a ton of empty slots on my chart). This is the basic idea I was trying to work with even though this particular one is not correct. I am trying to set my range only equal to the cells that do not = 0. Sheets("Graphs").Select ActiveSheet.ChartObjects("Chart 12").Activate Set graphdata = (Range("F30:IT31") <> "") ActiveChart.SetSourceData Source:=Sheets("Data").Range(graphdata), PlotBy _ :=xlRows ActiveChart.ChartArea.Select
View Replies!
View Related
Don't Plot Blank Cells In Bar Chart
I am trying to set up a bar chart that will not plot values where I have left the cell blank so that I don't have to keep resizing the source data / sorting the data whenever I add more data. I have tried putting NA() as the value as you would for scatter plots I have also tried leaving the category cell blank Neither of these seem to work. I have attached an example.
View Replies!
View Related
VBA Macro: Clear All Constant Cells In Range That Don't Have Formulas
is there a way to do this in VBA? I've also read: - Dave Hawley's recommendation of using: Sheet1.UsedRange.SpecialCells(xlCellTypeConstants).ClearContents from another thread (which is excellent!) - SHG's recommendation of using a named range, for example: Range("Inputs").ClearContents Given my limited knowledge of VBA, how would I now combine the two to write a VBA sub-routine that clears a named range entitled "Entry" on a sheet entitled "Data"? Would the following be the correct syntax: Worksheets("Data").Range("Entry").SpecialCells(xlCellTypeConstants).ClearContents
View Replies!
View Related
Sumproduct Syntax:. Don't Say 'yes', For Each Service
I have a large spreadsheet like this NephrologyPrinted resent 26.05.09 chased Nephrology28.04.09 resent 26.05.09Yes Nephrology28.04.09 resent 26.05.09Yes Nephrology28.04.09 resent 26.05.09Yes Nephrology28.04.09 resent 26.05.09Yes Nephrology28.04.09 resent 26.05.09Yes There are plenty of other columns, but these are key. The first column is the service type, of which there are 40. I want to have another worksheet that totals all the entries in the third column that don't say 'yes', for each service. So, in this case the first line of my new workbook would say: Nephrology1
View Replies!
View Related
If Blank Cells, Paint Yellow. If Cell To Left Is Less Than A Number, Don't Paint
I have a spreadsheet which contains 39 columns with a header row. The number of rows varies per spreadsheet. I'm running into a problem with highlighting(painting yellow) cells. Really the problem is selecting the cells to be highlighted. The macro is skipping some of my code completely and in other cases leaving some cells unhighlighted. 1. All blank cells in row AG should be painted yellow. 2. If column AF contains a cell with number 900 or greater, then the cell in AG (same row) should NOT be painted yellow. 3. All blank cells in rows AC and G should be painted yellow. Some spreadsheets may not contain blank cells. Problem with the VB is not performing the code after the first command. It will highlight MOST of the cells in AG but for some reason it is leaving a few blank ones unhighlighted. The blank ones are usually at the bottom of the rows. I have no idea why it would miss these. I believe the "ucase" tag is incorrect since its looking for a number but I am not sure what to put in its place and if I use something else should I dim it as ""? I've attached an excel spreadsheet which contains a sample to this problem. I've also attached what it should look like if my code worked.
View Replies!
View Related
Is There A Don't Count Function?
Right now I have a column of about 300 rows where I want to count how many actual names are in the column. I thought that COUNTA(c2:c313) would work, but it returned 312, while Im expecting it to return about 10. I think that the reason is because all of the cells in this column are pulling data from another sheet using this formula: =IF(Clients!I299="","",Clients!I299). Most of the data are blank cells Since of the rows in this column are empty, so I was wondering if I perhaps there was a function such as "DONTCOUNTIF" which would count the cells which didn't have a certain value, in this case "".
View Replies!
View Related
Only List Names That Don't Have A Value Next To Them
With the following list on Sheet1, on Sheet2 I want list only the Names that don't have a value next to them: Aaron Abigail 6 Alexander Alina 24 Allan Andrew 5 Ann That sounds so easy to explain, but I am baffled as to how you can do it with a standard if function without getting the following type of result (this list could be up to 200 names - I only want ones that don't have a populated field next to them): Aaron True Abigail Alexander True Alina Allan True Ann Can this be done with a groovy IF and INDEX formula, or is VBA (of which I have very limited skills) the only way to go?
View Replies!
View Related
Select A Sheet You Don't Know The Name Of Yet?
I have a macro that duplicates a hidden page, and names the duplicate sheet by cell reference. then the macro goes back and resets the forms that were used, what i would like to do is have it end selected on the newly created sheet, but i can't reference the sheet because i don't know in advance what the sheets name will be.
View Replies!
View Related
Values Don't Add Up
Do While WorksheetFunction. CountIf( Range(Cells(ActiveCell.Row, 16), Cells(65000, 16)), "X") Cells. Find(What:="X", After:=ActiveCell, LookIn:=xlFormulas, _ lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Rows(ActiveCell.Row & ":" & ActiveCell.Row + 1).Select Selection.Copy If Cells(ActiveCell.Row, 15).Value = Cells(ActiveCell.Row + 1, 15).Value Then Sheets("Sheet2").Select ActiveSheet.Paste Cells(ActiveCell.Row + 3, 1).Select Else Sheets("Sheet3").Select ActiveSheet.Paste Cells(ActiveCell.Row + 3, 1).Select End If Sheets("D (2)").Select Cells(ActiveCell.Row + 1, 3).Select Loop When comparing O3 and O4 in the pic above, the macro should copy and paste this into Sheet2 as the 2 values are equal. However, O3 and O4 rows are copied to Sheet3 instead. O6 and O7 works correctly in that they are copied to Sheet2. I have tried to compare the 2 values by entering =IF(O76=O77,1,"") in the first worksheet and obtained a result of 1.
View Replies!
View Related
Don't Exceed Certain Amount In Calculation
The MS State Tax Credit maximum per person, per year is $500.00. I need to multiply the monthly credit allowance by 12, then multiply that by 25%. This answer must not exceed $500.00. I need this formula for both the male and female. I can't figure out how to make the formula both a SUM and an IF. EX: (79-D22)*(D20*12*25%max $500)+(83-F22)*(F20*12*25% max $500)
View Replies!
View Related
Button Don't Get Deleted Completely
I have a problem regarding buttons.I have placed buttons on my excel sheet with a regular spacing between them.These buttons are placed dynamically on the sheet and I don't know what will be their names as user can add as many as they like.Now the problem is suppose there are two buttons in the range "A1:d8" and say now i delete the this range (using range("A1:d8").entirerow.delete) then buttons do not get completely deleted .A very thin line size button still remains on the sheet. How to remove the buttons completely......
View Replies!
View Related
Don't Run Macro On First Three Sheets
After searching through other threads I was able to gather the following code to copy worksheet titled Week 1 and paste it to the remaining 51 worksheets in the workbook: Sub CopyCols() Dim ws As Worksheet For Each ws In Worksheets If ws. Name <> "Week 1" Then Sheets("Week 1").Range("A1:N" & Rows.Count).Copy ws.Range("A1:N" & Rows.Count) Next ws End Sub The problem I am having is that I have 3 summary worksheets before the Week 1 worksheet and I need these sheets to not be affected by the above macro.
View Replies!
View Related
Show Only Rows That Don't Contain Certain Word
Looking for a VB solution to parse each cell in column B if a specific word exists then hide the entire row. in this case the word or phrase will be (inactive) each row will be different words but can contain ( like this... ___A___B____________________________________________C 1 44 this row does not contain the word but has other 3 2 23 this row does not contain the word but has other 5 3 21 this row does contain the word (inactive).................Hide the Row 4 26 this row does contain the word (inactive).................Hide the Row 5 43 this row does not contain the word but has other 6 6 20 this row does not contain the word but has other 9 and so on....
View Replies!
View Related
Displaying Cell Value But Don't Use In Calculations
I have a row of raw quality control data, but some are not in control and should not be used in the calculation of statistical data. There are not a lot of them, so I can manually select these (I use a red fill color for the cell to signify bad data), but I would like to be able to keep these data displayed but not have them included in the calculations.
View Replies!
View Related
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 Replies!
View Related
Don't Open The VB Editor When A Macro Is Executed
I have a query in Excel 2003. My question is: I have a macro created for my Excel. I have a short cut key to run this macro. When i run the macro, the VB editor is opened where i have written the code. My requirement is, I don't want the VB editor to be opened when i run the macro. I want the macro to be executed but the VB editor should not be visible to the user.
View Replies!
View Related
Sum Textboxes On Pages That Don't Exist Yet
How do I reference the values in TextBoxes on pages in a MultiPage form that have not yet been created? I need to add all of the first TextBoxes values on each page starting with page 2 and place the sum into the first TextBox on page 1. And do the same with the second and third TextBoxes. I have included a sample workbook to show what I am trying to do.
View Replies!
View Related
Don't Allow Duplicate Entries Across Multiple Columns
how i can set 'Data Validation' to stop me from entering duplicate values across multiple columns, i can set it for a single column i.e. A1 to A100 but i am after this but for something like A1 to H100.....? I am currently using this formula in Data Validation: =COUNTIF($A$1:$A$100,A1)=1 I need something to do this but for multiple columns.
View Replies!
View Related
Insert Shapes That Don't Cover The Grid?
I have tried to make cartesian coordinates (X)and (Y) using excel 2007. now I want to represent some shapes (circles, squares, triangles, and so one). My problem now is with formating those shapes. I use this path (Insert > Shapes),, but the shape I add cover the grid, I want (for example) a circle but I need only its circumference, and without covering the grid by its area. the same for other shapes.
View Replies!
View Related
Don't Update Links On Workbook Open
I have a workbook that has many cells that link to a server on the other side of the planet. If I manually open this workbook I get the "This workbook contains links to other data sources." And the option to "Update" or "Don't Update" If I choose to not update, everything works great. If I choose to update, the worksheet takes 10-15 minutes to open. I have tried to go to tools-->options-->Edit and uncheck "ask to update automatic links" but this makes the default behavior updating. I want to open this workbook using a scheduled task and a batch file, and have it not update, and then run a macro. Any suggestions on how to do this?
View Replies!
View Related
Link Formulas Don't Update Automatically
I have 2 WorkBook, Division.xls and Department.xls. The Sheet1 in the Department is linked & getting the values from Sheet1 in the Division & works fine. But if there is a change in Division workbook, its not affecting here immediately. When i close the Department.xls and open again, i can find the latest values. I thought the links to the other sheets or workbooks will be updated when i press the save button. So I used the SendKeys "^S" in the VBA. But it fails. Is there any way to mention through VBA code to update the recent values to the Department workbook from Division.
View Replies!
View Related
Don't Display Blank Entries In Drop Down Menu
If i make the listfillrange of my combobox a named range, is there a setting in the combobox that allows it to ignore any blank cells in the named range. Lets say my named range is cells A1:A5 but only A1 has a value, the rest are blank. The combobox drop down menu will display the value of cell A1 and then 4 blank rows. Can i make the combobox igoner the blank rows and only display cells A1 in the drop down menu?
View Replies!
View Related
Vlookup Array Formula Too Slow - Don't Want VBA
I have this formula: =VLOOKUP(E24,OFFSET(Data!E$23,0,0,MATCH(TRUE,Data!E$23:INDEX(Data!E:E,MATCH(9.99999999999999E+307,Data!E:E))="",0)-1,14),14,0) in column e and looking up from a sheet called Data!. This formula looks up off of column e into the data worksheet from the point where I insert a blank row at a chosen point in the data. The problem is that this formula needs to calculate in excel where is shows in the bottom left of the screen...calculating 5%...50% etc. and I have to wait. Does anyone know how to rewrite this formula so it does not have to re-calculate after each move? This formula above requires a CTRL+****+ENTER. What the formula above does is simply looks up into the other worksheet from column e and looks up only into the data where I insert a blank row. So it creates a lookup range at the point where the blank row is inserted.
View Replies!
View Related
Web Queries Don't Refresh During Code Execution
I am writing a routine to retrieve multiple stock quotes by looping through a bunch of sheets and refreshing a bunch of querytables that access the web. My data source (Livecharts) is often clogged up and queries will fail or get lost in space so after initiating the queries I wait for a while using a waitable and then check to see if each query has completed or is still refreshing. If they are still refreshing I cancel them and refresh again. Or at least that was the plan. What I have discovered is that as long as the vba code is running the results from the queries do not come back into excel. As soon as the code is exited or I go into break mode in the debugger the queries complete. Is there some way to get the queries to refresh during code execution? Here is a code snippet that shows what I am trying. I have defined a Class Module defining an "EventfulQTable" and it's associated methods and properties so I can have a QueryTable with events. The sub Wait uses the windows API SetWaiTable command to pause for a specified period of time. It includes a DoEvents command which I thought would allow the returning queries back into Excel but no such luck. Sub Refresh_Queries() Dim SheetNum As Integer, ListSheet As Integer Dim eqtQT As New EventfulQTable Dim QueriesDone As Boolean SheetNum = 1 ListSheet = 12 QueriesDone = False On Error Goto QueryError
View Replies!
View Related
Searching A Column For A Word And Deleting All Rows That Don't Contain It
I am trying to create a search function in a database I have made. I have a userform with drop down boxes that people can choose what they want to search for. Once they have chosen and clicked the search button I want it to search only in the relevant column, i.e. search for the word in the dropdown for "engine" only in the "engine" column of the database. Once it has found the appropriate cells with the answer, I want it to delete all other rows that don't include the answer. Once it's done this it needs to do it again for the other fields, unless they are empty. I have used 'If engine_type <> "" Then' as the starting point so that it only searches chosen fields.
View Replies!
View Related
Download A .txt File And Don't Lose The Formatting?
How can I download a .TXT file and don't lose the formatting? I found an example: Option Explicit Private Declare Function InternetGetConnectedState Lib "wininet" ( _ ByRef lpdwFlags As Long, _ ByVal dwReserved As Long) As Long Private Declare Function InternetAutodial Lib "wininet.dll" ( _ ByVal dwFlags As Long, _ ByVal dwReserved As Long) As Long Private Declare Function InternetAutodialHangup Lib "wininet.dll" ( _ ByVal dwReserved As Long) As Long..........................
View Replies!
View Related
Formulas Added From Macro Code Don't Update
I used VBA to write a number of formulas into various cells in a workbook. After the VBA is done running, I change the values in the cells that are referenced by the cells I wrote formulas in. The formulas do not update after I enter this new information. My calculation is set to automatic, and F9 doesn't help.
View Replies!
View Related
Delete Blank Rows :: That Don't Contain Visible Numbers Or Letters
I'm trying to clean up several long worksheets by removing rows that don't contain a visable number or letter. I want to remove all rows that only contain either empty cells, formulas that evaluate to a empty text string (""), cells that contain error values, or cells that contain zeros, or any combination of the previous. I've stumbled onto the following, but it does not work.
View Replies!
View Related
Pivot Table Total Rows Don't Match Data
I have run a pivot table state, city, address, floor and all the maths works out fine.. but the name in the 'total' rows, does not match the name in the data rows.. e.g. texas, dallas, 57 oilrig street has total rows saying CA toal,Newark total, 560 mission street total (obviously split across the appropriate rows for each total) but in each case, all those square feet, or dollars add up perfectly, for the data rows above them. double clicking the totals shows no sign of the rogue location... but these same entries are in EVERY set of totals in the pivot table! POSSIBLE clue.. I generated the pivot at home, on Office 2003, and am now viewing it at work in 2002 version. (buit this could have nothing to do with it at all. Am I missing something obvious, or do i have good reason to pull my hair outr in frustration (first time this has happenned in a year of doing these reports daily)
View Replies!
View Related
If 1st Condition Met Don't Execute 2nd IF Statement
I'm having a problem with negative values. Somehow if the pitchX is -11.1877, let's say, and it is the right value, the code will still perform the 2nd if statement, when it should not. Am I using the wrong data type? Or is there anything I'm missing out? Dim pitchX As Double If pitchX > 10000 Then pitchX = pitchX / 1000 End If If pitchX <> Range("C8").Value Then With Range("C8") .Font.Bold = True End With End If
View Replies!
View Related
Custom Ribbon Menus Don't Work Until Save, Close & Re-Open
Created a new tab and menu buttons on the Ribbon in Excel in 3 separate .xltm files. Everything worked great through all of the development and testing, but when we went live we got reports of a bug. Here is what is happening... User opens the template file and immediately saves it as a .xlsm file with a new name in a new location. The user then does various amounts of work in the file. When the user needs to use one of the tools on the custom tab, it is as if the buttons are inactive or not associated with any actions. The tab and buttons are all visible, the animation of the button being pressed works when any of the buttons are clicked, but nothing happens. The user can close and reopen the file and the ribbon buttons will work correctly, but not until they close and reopen. Upon digging into the problem, we found that if the user clicks on the custom tab before they save the file, all of the buttons will work correctly after the save. But if the tab is not activated before the save, they dont work until a close and reopen. The XML code is good because if I place it in a add on file (.xlam) and trigger it from the main file, there is no problem. But this is not a good solution since the add on file needs to be located in the same file that the main file is run from which can be any number of places.
View Replies!
View Related
Stop Update / Don't Update Requestor Message
I have some VB code which sequentially opens over 200 workbooks to extract data from each and populate another workbook. These workbooks do have links to other workbooks in them. For some reason when some of these workbooks are opened I get a requestor window asking whether I want to Update or Don't Update the data. I always want to Update the workbook and believe this can be done in VB by hiding the requestor?
View Replies!
View Related
Deleting Rows Macro :: Rows That Don't Contain Word ...
I got a code to delete all rows in the sheet which contain the word "DETAILS" but I now want to delete all the rows that do not contain the word "DETAILS" My code if needed is: Sub Find_details() Dim rng As Range Dim what As String what = "DETAILS" Do Set rng = ActiveSheet.UsedRange.Find(what) If rng Is Nothing Then Exit Do Else Rows(rng.Row).Delete End If Loop End Sub
View Replies!
View Related
Copy Columns Between Worksheets If The Columns Don't Match
I have 2 worksheets in which I have to copy one column of cells from one to the other. The problem is that the "main" worksheet lists nearly 3,500 clients and I have to copy numbers into the "main" worksheet on weekly basis from another worksheet that lists only about 1,100 of those 3,500 clients. Both client lists are in alphabetical order, but I obviously cannot just copy one column into the other, because then the numbers won't be matching the correct clients.
View Replies!
View Related
Choose Sheets That Save Or Don't Save
I have 120 sheets in my workbook and I only need 5 of them to save when there are changes, the other 115 are were data is pulled from. Is it possible to tell excel not to look at a sheet when it saves? I'm just after making it save faster. This is going to be a workbook that " moves" a round a lot, so I want to the 115 sheets in the same workbook.
View Replies!
View Related
|
TRACKER
