Dotted Gray Lines Around Cells In Worksheet
I'm working on a worksheet that another person started. For some reason there are dotted gray lines around every single cell in the print area in the print preview (no idea what this person did or who this person is, so I can't ask them). Anyhow, I need to get rid of these dotted gray lines. Clicking on no borders does not work.
View Complete Thread with Replies
Related Forum Messages:
Dotted Lines Around ListBox Items
ListBox2 (right) is a copy of ListBox1 (left), yet when I populate ListBox1 I get dotted lines separating each item (Photo #1.)
When I populate ListBox2 it comes out clean (Photo #2.)
what I might need to change so that ListBox1 does not have the dotted lines around each item?
Scrolling up and down w/ListBox1 will remove some of the dotted lines.
Evaluate A 'dotted' Number Using Formulae (not UDF)
Is there any way to 'evaluate' a dotted number (similar to an IPv4
address but not just those) using standard worksheet formulae (ie no
UDFs, VBA, or ATP functions)?
To make it a little more complicated, I need to be able to evaluate
not just IP addresses (although that would be a use) but also a
'number' such as 9.0.2 compared to 8.7.5 so that they can be sorted
and / or compared.
The views expressed are my own, and not those of my employer or anyone
else associated with me.
My current valid email address is:
This is valid as is. It is not munged, or altered at all.
It will be valid for AT LEAST one month from the date of this post.
If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:
Code For Gray And White Rows
I have a spreadsheet where the user wants gray and white lines to occur starting from row 36. Meaning row 36 is grey row 37 white 38 grey etc. to make it look like old accounting paper. The problem is there is a macro that runs to hide the zeros so I cannot preformat the rows because they will change along with the worksheet. Also, the coloring should stop when there is nothing more in the rows, usually around row 100.
Remove Dotted Line Around Option Button
I have created a questionnaire with multiple answers in a userform with multipage.
Everything works fine except on about 10 out of my 30 questions, one of the four answers (all opition buttuns) has a fine dotted line around it.
I really have no clue as to where this line comes from. I have tried deleting the option button and replacing it with a new one. This helped for the concerned button but now the dotted line appeared on another answer of the same question! I have done it several times and each time the dotted line simply shifts from one answer to another but never dissapears! The odd thing is that this only concerns 10 questions, the other 20 are fine...
Return Multiple Lines From Workbooks Onto On Worksheet
I looking at attempting to combine 26 spreadsheets all into one with a macro. Basically i have 26 spreadsheets detailing all items placed into different suspesne accounts, these are on going so they can be up to 2000 lines long. All 26 spreadsheets are contained in one folder with a backup of each.
Columns B - F detail the payment being applied.
Columns G - J detail the payment being removed.
I was hoping the would be a way for a macro to look into each one and if there is data in column E (value) and not in column I (vlaue removed) then return the whole line onto a new worksheet. So basically after i have run the macro i would have one worksheet with all outstanding items on instead of having to open each one and copy and pasting.
The lines will always vary on each workbook as to where the outstanding payments is and im using excel 2003.
Add Multiple Lines From Cells To Cell Comment
I am trying to add multiple lines in comment. problem is the text to this mutiline comments is stored in a range
Range("J15").Comment.Visible = False
Range("J15").Comment.Text Text:="Line1" & Chr(10) & "Line2"
(This is recorded). assuming that range "A1:A15" is where text for this comment is stored from a different VBA program.(15 number of line is the max i have programmed to accept). assuming there are 10 cells currenty full. how can i add this 10 text lines to a comment box.
Want Whole Row To Shade Gray When Drop Down Item In Row = "completed"
I have a a worksheet with a drop down menu in one column to show various completion status options (Not started, In progress and Completed). I was able to use conditional formatting to change the color of the cell to gray when I choose "completed" from the drop down menu, but I would like the whole row in view (about 8 cells across) to shade to gray when I choose "completed" from the drop down - not just the completed cell. I'm thinking an IF statement would work, but I don't know how to use an IF statement to shade a range of cells a color.
Copying And Pasting Cells From A Worksheet Into A List Format In Another Worksheet
I need help with a macro for copying and pasting of cells. I believe this should not be a problem for the Excel VBA experts, but for someone who can only record macro, I'm really at a loss.
Attached is a sample file, where sheet 'Source' is an example of the sheet from which data are to be copied. The other sheet, sheet 'Final' is an example of the final format that I need. The reason I'm doing this is I'm planning to upload my data into Access and so I need to convert them into a list format.
List of target columns in sheet 'Final' and source cells in sheet 'Source':
Column A: Biz ID - not sure if I really need this, by right it should be listed automatically once I paste the data
Column B: B2 of 'Source'
Column C: B2 of 'Source'
Column D: B1 of 'Source'
Column E: row 6, relevant column
column F: column K
column G: row 5, relevant column
column H: the specific amount
So basically I'm creating an entry for every amount in the table.
Copy Cells And Range Of Cells To New Worksheet
I have an excel workbook that I have created to use in the creation of purchase orders. I have a spot for the vendor in cell D10 and my items are in the range of B17 to N17.
What I would like to have is a macro that I can assign to a button that does the following.
I have a worksheet called "Purchase Order" that has all the information in it.
When you click on a button named "Process", it will take everything entered into B17 - N28 and copy it to a worksheet named "PO# History" starting in cell B2 and moving down. Each time I click on "Process" I need the information in the range above to get entered into the next empty line on the "PO# History" worksheet.
This way I can maintain a list of items purchased and then play with them in a pivot table/graph and such.
Also, What I need it to do also is when process is clicked, the macro needs to pull the vendor name from cell D10 and put it in column A on "PO# History" for each row it adds from B17-N28.
All Used Cells On Worksheet
ActiveWorkbook. SaveAs Filename:= _
"C:Documents and SettingsSimonMy DocumentsBook2.csv" _
, FileFormat:=xlCSV, CreateBackup:=False
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Cells. Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Select
The first part exports part of a worksheet as a CSV, the second part finds the last cell used in a worksheet.
I would like to use the second part as the end of the range referenced in the first part...so the range would be from B1the results of the 2nd part)
Move Cells To Another Worksheet
I have whats going to be a very large "Input" worksheet that has several headings of columns. One of these columns is "category" as in parts of a vehicle, i.e. steering, wheels, rod-ends and so on. Now what I would like to do, is once I type a new row of information in, to have the filter go through and copy said category row into another worksheet where I can have all of them separated on there own.
Here is a picture of my current spreadsheet. So basically I want to be able once I save the file, that it goes through and where it detects Rear-End in "B18" I would like it to select row 18, copy all of its cells and move them into the worksheet Rear-End
Getting Part Of Name Of Cells In A Different Worksheet
Is there a way I can write a formula to get names from a different worksheet if I type part of that name in the active sheet.
Say Sheet1 contains cells like
1026 Wild hard nuts fruits -Named Fgl1026
1028 Wild Soft nuts fruits -Named Fgl1028
In active sheet, when I type Fgl1026, I want the next cell to left of the activecell to show Wild hard nuts fruits.
Separating Cells In A Worksheet
Set up a worksheet so that it has 2 separate sections in the same worksheet ????? I am wanting to have 2 different sets of information in the same worksheet - and need the columns to be different sizes based on the information i want to put in. Is there any way to have this done - as I know that if you change the column width it does all columns - and i only want to do select columns. For example for section 1 - i would like to have the column widths as follows:
A1 = 20
B1 = 40
C1 = 10
D1 = 20
E1 = 40
A3 = 20
B3 = 40
C3 = 10
D3 = 20
E3 = 40
This is where i want to put details such as names, dates and results.
Then for section 2 - i would like to have the column width as follows:
A6 = 10
B6 = 45
C6 = 15
D6 = 30
This is where i want to have a set of questions to answer.
Hiding Cells In Worksheet
I've created an Excel spreadsheet (Excel 7) to help me price sign printing jobs consistently. In my spreadsheet a few cells allow me to enter markups for materials & labour rates. These are used for calculating the retail price.
I would like to give my spreadsheet to some of my customers so they can calculate the retail price of a job themselves BUT, I would like to hide the contents of those few cells where I fix the markup. How can I achieve this?
Clear All Cells On Another Worksheet
I have the following code in a Excel 2003 spreadsheet with hope that I can clear contents of unlocked cells in one excel file from the code stored in another Excel file.
' CLEARSJCCOUNT Macro
' Macro recorded 02/21/2008 by Steve Keene
I get the Subscript Out of Range error window when it hits the first line of code.
I've reviewed this via searching for other posts, but none seem to solve the problem directly.
Get Address & Worksheet Name Of Cells
way to transform the commun Excel VBA reference to a cell
to the reference inside a cell, like
The idea behind is to introduce an Excel formula in a cell, with a reference to another cell. Thus, in my macro, the reference is
, and in the formula string, the reference must be the form "A1".
I now I can programm a function for it, but before I would like to know if there isn't a easier way like an integrate Excel function to do it faster and better.
Copy Used Cells To Another Worksheet
I have data that I need to copy into a new worksheet, that is fine, no problem, however, the range of cells that I need to copy is different each month, and as I am collating several different sheets into one new sheet, I need to be able to select the active range, I.E, the cells that contain data. This month the range is A2:P56, however, next month could be A2:P123. Is there any way I can use VBA to select the active range? Sorry if this is explained badly, its difficult for me to type what I want to say!
Clear All Worksheet Cells
Gotta question about destroying the contense of a worksheet. If for instance I got a macro which uses a different source everytime I run it, and this macro gives a result in a result worksheet. How can I possibly delete the contense of the result worksheet automatically before the execution starts? What I mean is: without selecting all the cells and pressing delete or either using a for loop to go through all the worksheet cells. Is there any function which clears the contense of a worksheet? (without deleting the worksheet itself)
Protecting Worksheet But Only Cells
I have a spreadsheet with about 2000 lines of vba code. My application needs to be protected so users don't mess themselves up; however, I really only needs the contents of cells to be protected. I have many macros that run, and in order to do this I need to unprotect and re-protect the worksheets. My problem is this: when I re-protect the worksheets, everything is locked up. I would like to allow users to change column widths, hide and un-hide rows, etc. I can't find the parameters needed to do this more controlled type of locking. Is there a list somewhere of all the parameters that can be passed to the Protect method? Here is my current subroutine:
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Referancing Cells And Populating Another Worksheet
I have two worksheets. One worksheet is pulling in data from an OLAP cude. I have it configured as a pivot table. The other worksheet contains my dataset. Both worksheets are in the same file.
In my datasheet worksheet I would like to pull in data from the other worksheet. I would like to be able start a few cells and then drag so that it autopopulates the values. Here is the formula I'm using
it poulated with the values
then I select the 3 cells and drag so that I can autopulate the remaining values. It enters the formulas
what I really want is
Is this possible? How can I do this?
Copy Cells Between A Certain Range Into A New Worksheet
I had originally posted this question on another thread since the problem i've been trying to solve is quite similar to the discussion on that thread.
I have a workbook with only one worksheet. This worksheet contains data in only the first column. However this data can be either tab or space delimited. The first few rows are junk data which i am not concerned with. I have a header and footer row and the useful cells are in between these.
What I am trying to solve is this:
1) Add a new worksheet, rename it to "COPY" and place it after the original sheet. This I have been able to achieve using the code below.
Dim PageSheet, CopySheet As Worksheet
Set PageSheet = ActiveSheet
Set CopySheet = ActiveSheet
CopySheet.Name = "COPY"
CopySheet.Move After:=PageSheet ...............
Compare Worksheet Cells And Return Value
I'm trying to compare cells in two spreadsheets and return a value to another cell in one of those spreadsheets.
Column A - NAME (Contains Data)
Column B - AREA (Contains Data)
Column C - PLAN (Contains Data)
Column A - AREA (Contains Data)
Column B - NAME (EMPTY)
Column C - PLAN (EMPTY)
I need to compare Spreadsheet_1 - Column B to Spreadsheet_2 - Column A and then fill in the cells in spreadsheet_2 with the values from spreadsheet_1 column A and column C.
Hide Worksheet If Certain Cells Empty
I am trying to write a code that would hide when certain cells in the worksheet are empty. Also the sheet name should start with a -.
This is the code i have so far. The thing is that the sheet will always hide. The criteria on the cells doesnt work.
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If Left(ws.Name, 1) = "-" Then
If Not Range("I9").Value = "" Or Range("K9").Value = "" Or Range("M9").Value = "" Or Range("O9").Value = "" Then ws.Visible = False
Application.DisplayAlerts = True
The code now hides all shees starting with "-". It does not take the cell criteria into account. Why?
Other point is that these cell references are just a few of what it should really be. How do I make this easier for myself to write the code. Point is that these cells come in row 9 (like the example) then in row 11 then in row 15, 17, 21,23, etc. Also the columns jump with uneven steps.
CELLS() - Fish Information From One Worksheet To Another
I'm trying to fish information from one worksheet to another.... Here's my sub, what's my SUBcranial problem?
Private Sub bt_PopulatePage_Click()
' Inventory Data Transfer to Report Structured Page
Sheets("Liquor Data").Select 'Switch to Liquor Data Sheet
Dim x 'Line Numbers x = Liquor Data Line
For x = 2 To Last_Row("B") 'Start at the first Liquor Entry Line
t_liq = Cells(x, 2) ' Liquor Name in Starting in B,2
t_Class = Cells(x, 4) ' Class starting in B,4
t_type = Cells(x, 5) ' Type Starting in B,5
t_dist = Cells(x, 8) ' Distributor Starting in B,8
Sheets("Inventory Form").Select 'Select Inventory Page
Cells(x + 7, 1) = t_liq
Cells(x + 7, 2) = t_Class
Cells(x + 7, 3) = t_type
Cells(x + 7, 4) = t_dist
Using Indirect To Sum Multiple Worksheet Cells
These do not:
Where Sheets refers to =Sheet1!$A$1:$A$2
For the last two ONLY Sheet2 is summed, not sheet3
Copying Row Into Worksheet After Populating Cells
I have the following simplified example:
Table with three columns with a growing number of rows. The user enters data into each row, once user enters the last value and hits "Enter" on the final cell (column c) I want a script to evaluate the row and copy the entire contents of the row onto an existing different worksheet. The first column (A) contains the attribute that will be the condition that determines which sheet to paste in
For example, in my attached file, the first row contains the value "Square".
I want that row be copied and pasted into the 2nd tab of the worksheet (reserved for "Square" data) on the next free line. Simiarly, Triangles should be pasted on the thrid tab. Note however that i think the data should be copied line-by-line as the user enters it, which obviously isnt the case for the example dummy data, its just there to simulate my setup
Copy Specific Cells To Another Worksheet
I'm creating a simple program that copy one or more specific cell values and place it on a specific cell in another sheet using loop to make it easier... I'm having a difficult time trying to figure it out..
from sheet1 A1:A5
A | B | C
and place P45, P47 and P48 on another cell, to be specific in C1,C2 and C3, in a different sheet
A | B | C
Copy Found Cells To Another Worksheet
I am looking for a visual basic script (macro) wherin I can copy fields from one sheet based on condition to main sheet. Sample sheet is enclosed as sample.xls. Here is what I intend to do: two tabs are there in sheet. master and work.
Need to check in "work" tab sheet if x name exists and if yes then copy three cells namely Input, Direct/.Indirect and code and paste all three cells in master sheet in front of X name wherever x name appears in resource name column.... Ultimately work sheet will be searched for all names which are in Master Tab and three field will be filled accordingly. Finally all the resources in resource Column will have three addditional field in mster sheet taken from work sheet.
Sort Range Of Cells On Each Worksheet
I have been trying to sort the cells in a worksheet, but am unable to properly select the range of cells I need
For Each ws In Worksheets
If ws.Name <> "MySheet" Then Goto nxt
If ws.Name = "MySheet" Then Worksheets("MySheet").Activate
Range("A1:F" & totval).Select
Selection.Sort Key1:=Range("F1"), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
the code mentioned above throws an error when it gets to the range.select line. I cannot simply say usedrange.select.
It keeps throwing error 1004.
Please ignore the "& totval" in above code. That was my attempt at specifying the last row number, which i specified in the totval variable
I dont know how exactly to specify the range of cells containing data in the activated worksheet above, and then select them for sorting.
Copying A Series Of Cells To Another Worksheet
is it possible to copy various cells from one worksheet to another. I have a cover sheet holding various jobs for different individuals, data validation has been used to set up various drop downs for the peoples name etc. On selection of an individuals name i was wondering could various cells on that row be copied into a seperate worksheet with that individuals name being the tab name? I have tried using the " Lookup" function but im not getting too far.
i have a list of about 2,000 rows of text going down vertically, but out of that 2,000 there's only about 1,500 actual items - the rest are duplicates.
how would i go about eliminating the duplicate strings of text quickly?
Unhide All The Lines
I have a file that contains at least 35 sheets. All of the sheets have some lines that are hidden. I tried selecting all the sheets and unhiding everything but it didn't work because some sheets actually have filters in them and the filter line is also hidden.
Add 2 Row After Each 9 Lines And Calculate A
I need a macro that add's after each 9 lines 2 new rows. The data I've it's not the same every month, one month I can have 27 lines the next one I can have 90 lines for exemple.
I need also to calculate in one of those added rows this "=B8+B4-B6". This calculation is to be apllied in each block of nines lines.
Comparison Of Lines
There are four lines of numbers, in all line 8 numbers.
1 1 2 2 3 1 1 2
1 2 3 1 2 2 2 1
2 1 1 3 1 3 1 2
1 2 2 2 3 1 1 2
I would like to find a function that will examine, if there are similar lines according to my definition, and several.
my definition is 7
For example that I gave, the result in the wanted function needs to be, yes (Line 1 and line 4) and several-One time.
Two Lines In A Msg Box On Open?
I need a msg to pop up when a worksheet opens. Pretty simple:
Private Sub Workbook_Open()
If MsgBox("My msg goes here!") Then
But I need to be able to have multiple lines, maybe a short paragraph.
How do you get it to allow additional lines?
I have a text file which is wrapped and i am trying to read it through VBA. I want to combine wrapped lines in one line
Header Via Vba With 3 Lines
I manually added a center header to my report that take 4 lines
So I would like to repeat this using vba
Line1: Looney Tunes, Inc.
Line2: Bugs Bunny Subsidiaries, Inc.
Line3: Consolidated Rollup
Line4: FY 2007
Furthermore, I would like to read this from cell(s) as at least a 52 page report
and lastly I would like to alternate font sizes
Line1: 18 pt
Line2: 18 pt
Line3: 16 pt
Line4: 14 pt
Protect Worksheet But Allow Selection Of Locked Cells
I am fairly new to Macros and such so please bare with me. I work with workbooks that have 30-50 worksheets in them. They are fairly complex and we send them out to our agents to use and need them protected so they cant change anything. I found how I can protect all the worksheets with one macro. There is one more part that I would like to add to this macro that I cant seem to find an answer for on these forums (Yes I have spent the better part of the day looking).
When you manually go to protect a page. It asks you to type in your password for the first time and below that there are a bunch of boxes that can be checked or unchecked. I need the first box "Select locked cells" to be unchecked. It is checked by default. Is there something I can add to my macro to uncheck this box?
This is the topic I used to create my Protect/Unprotect macro.
Macros to Protect/Unprotect Worksheets and Workbooks
Cells With Fill Alignment Duplicating Over Worksheet
As a simple example, I have three columns (A,B,C). In both Column A and B they have single word text in them, but in Column C it is a paragraph of words that I format the cells to 'fill' so that it is all tight and concise when viewing the worksheet.
Afer I have saved the document and have closed it when I reopen the document. Cells from column C have randomly duplicated themselfs throughout the entire worksheet,but only onto columns A,B,C (where there is pre-existing text). As the random cells get duplicated it overrights the original text (results) as it does it, so once I open up a document and see this it is to late. this is a continual problem that I cant find a resolution for.
When Protect A Worksheet Then Only UNLOCKED Select CELLS
Ok I've spent over 2 hours reading MANY peoples issues with this "BUG". Back in 2003 was the earliest... no one has an answer that works.
When you protect a worksheet and only have UNLOCKED CELLS selected...somehow through loading and unloading the file... you can select locked cells....
I can not find a pattern but many people have had this issue all with no concrete answers.
Just by loading and saving, exit and loading and saving, exit and loading.... i can now select locked cells that I previously couldn't. (I can't do anything as it's still protected...) It's a pain because it wrecks my tab flow.
Inputbox Method: Cannot Select Cells In Worksheet
I'm using an inputbox to let the user select a range of cells. Here's the line
Set MyRange = Application.InputBox("Select cells with numeric data", Type:=8)
When the inputbox comes up, it won't let the user click any cells (or anywhere in the worksheet, for that matter). The cell/range reference has to be typed in manually.