Dotted Gray Lines Around Cells In Worksheet

Jan 11, 2010

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.


Dotted Lines Around ListBox Items

Dec 3, 2009

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.

View 9 Replies View Related

Extract Numerical Pixel Gray Values From Gray Scale BMP Image

Oct 21, 2013

I have a *.bmp image file. It is gray scale only. It amy be up to 1000 x 1000 pixels.

I want to extract the numerical value of each pixel and deposit the values into an Array for further processing.

View 1 Replies View Related

Clear Contents Of Dotted Selection

Apr 24, 2014

I'm playing along with Excel. I discovered it doesn't like cutting and pasting in VBA. This screws up formulas in cells.

It much prefers copying and pasting. So this involves an extra step, going back to the cells you just copied and clearing them.

So I have a selection of cells and I just copied them to a different location. Naturally the location I just copied it to then becomes selected.

I would like to go back to the cells that have the dotted selection around them and clear them. If I simply use Selection.ClearContents it will clear the currently selected cells, NOT the dotted selection.

Command to clear the contents of the dotted selection?

View 4 Replies View Related

Evaluate A 'dotted' Number Using Formulae (not UDF)

Apr 12, 2006

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
email address.

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:


View 10 Replies View Related

How To Gray Out Unused Columns

Oct 24, 2013

if I can gray out the unused columns. I am creating a template that use only column A to J. I don't want user to be able to see or scroll to the unused columns. Is there any command in the setup that can gray out the unused columns? or maybe VBA codes?

View 2 Replies View Related

If Cell A# Is Occupied Gray Out B#

Feb 14, 2007

How do I gray out one group of cells or cell if another is occupied?

View 9 Replies View Related

VBA - Cell Copy Leaves Behind Dotted Line

Feb 17, 2012

I'm using the following code

Sub UpdateValues()

' Purpose of this Module is to properly update all the values of the OEE Worksheets.

Dim ws As Worksheet
Dim r As Range

Set r = Range("C30:H77")

For Each ws In Worksheets

[Code] ...........

However, I'm getting a dotted line fragment left over

I suspect it's because I used .Copy in the formula. Is there a better method?

View 7 Replies View Related

Remove Dotted Line Around Option Button

Sep 7, 2007

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...

View 5 Replies View Related

Code For Gray And White Rows

Dec 29, 2006

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.

View 9 Replies View Related

Cell Formatting - Turn Entire Row To Light Gray

Dec 13, 2013

IF the list value in D2 is = to MT,GP,or H then I need the entire row 2 to turn light gray.

View 6 Replies View Related

Excel 2010 :: Why Are Lines Vanishing From Worksheet

Nov 18, 2011

I am using excel 2010, windows 7. When i make a new worksheet or update it randomly blanks out lines that already have information in them. It blanks out the row number also.

View 1 Replies View Related

Return Multiple Lines From Workbooks Onto On Worksheet

Jan 21, 2009

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.

View 9 Replies View Related

Excel 2013 :: Merging Selected Lines In Worksheet To One Line - Removing Dupes?

Aug 12, 2014

I merged about 15 adresslists from media contacts to one excel list. Each list had a name i.e. music, health, theater, etc. and the same logic in colums. I added a few columns and have 1 large list now.

As some journalists write about music & health & theater, architecture, etc. they are listed up to 10 times in the new list now. But the "genres" from the original list i.e. music, health, theater, etc. are in different columns. Some of the lines have empty fields (i.e. no address or mail)

All I want to do is have one line with all the information of all 10 lines in it, merged, dupes removed:

company - firstname - lastname - Adress - Mail, etc. : genre: music - health - theater:

I atteched an example of the full list and the result i want

View 9 Replies View Related

Border Lines Are Not Showing For Some Cells?

Nov 7, 2005

I have an extensive spreadsheet with several columns and all showing borders where each cell/column begins and ends. However there are a few cells where the break with the next cell does not show the line.

I have highlighed the cell in question, gone to format/cell/borders and everything looks fine. Black color + format border shows square with all sides of square showing.

View 3 Replies View Related

Add Multiple Lines From Cells To Cell Comment

Aug 20, 2007

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.

View 5 Replies View Related

Want Whole Row To Shade Gray When Drop Down Item In Row = "completed"

Jul 16, 2009

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.

View 3 Replies View Related

Worksheets Drawing Data From Target Cells Without Moving If Add Lines?

Feb 19, 2014

I have one excel file sheet in which we enter client data and another page in the same file that formats the data. Each new row is another client.

It is a simple formula in the data formatting sheet ='data sheet'A23 for example.

The problem is when we add client data above A23, say 2 rows, the format data page will follow the original cell and now say ='data sheet'A25.

How do I keep the cell as ='data sheet'A23 no matter what I do the original page?

View 1 Replies View Related

Merged Cells: Format The Cell So That If There Isn't Two Lines Of Type In The Description It Remains Aligned With The Quantity And Price

Apr 22, 2006

I've attached part of the file I'm working with, and can't figure out how to align the text to the top in the merged cells titled description. How do I format the cell so that if there isn't two lines of type in the description it remains aligned with the quantity and price

View 2 Replies View Related

Excel 2007 :: Multiple Lines In One Cell To Multiple Cells?

Mar 24, 2012

I have a column of cells with Customer info and address in each row, that I need to move into multiple columns. ie Cust name, acct #,address, city,state, zip the problem is ALL of the info for each customer is in ONE cell.To be clear, cell B1 has complete name address etc for one customer, B2 has the info for another, B3... on and on. To complicate matters as in the example, there is not a pattern in the lines. the first example shows the info beginning on the 2nd line of the cell, but the 2nd example shows info in the first line. I have tried the text to column fixed width. Didn't work because names and addresses are of varying lengths. Then I tried delimited, but there isn't a common separation between different data. I am using excel 2007. FYI I did go through many many threads and found some that were close to my problem but not close enough to work.




View 7 Replies View Related

Create A Separate Worksheet That Has The Data In And Reference Cells On Budget Worksheet?

Feb 7, 2014

I need to reference the projected and actual expenses from the total on worksheet 'expenses' So i did so, however, if I change the drop down on the expenses worksheet to only display housing data, then the projected and actual expenses on my budget worksheet changes as well to the new data portrayed on the expenses worksheet.

I need to reference the cell, without it changing when I change the category display, but I need the cell to change accordingly if I enter new data in the overall tablet on the expenses worksheet.

Or do I need to create a seperate worksheet that has the data in and reference my cells on my budget worksheet to that new worksheet?

View 1 Replies View Related

Search Keyword In Worksheet / Copy And Paste Adjacent Cells To New Worksheet

Sep 24, 2012

I've been tackling this data capture/paste issue for a week or so. I found the string below which does provide a good foundation for my challenge. But, my basic level of understanding macros limits my modifications to meet my needs.

[URL] ......

I have 20 worksheets in my master file corresponding to Excel files individual associates will update weekly. After the associates have updated their individual files for the week, I want to capture the data entered and paste values into a master file containing a worksheet for each associate (sharing the same name as the individual associate file). All of these files are housed on team SharePoint sites.

I need a macro to perform several steps after clicking a "Run Update" macro button in the master file:
Open individual associate fileIn master file, search for each Initiative listed in column B (starting cell B3) in the individual associate file (in column B starting at cell B11)If Initiative is found in individual associate file, copy adjacent data in columns D:J for the respective rowIn master file, paste values to the corresponding Initiative row for the corresponding week's worth of dataIf Initiative is not found in the individual associate file, move to the next Initiative listed in the master fileRepeat these steps for each individual associate file

Linking would be the easiest way to accomplish this if I wanted to have a multitude of weekly individual files for the associates. However, I'd rather each associate have one file for them to update (basically overwriting their previous week's entries).

I need to ensure the paste values corresponds to the appropriate day of the week. In simpler terms, if the date in the individual associate file in cell D9 reads Oct 1, 2012, the data captured from that row needs to be pasted to the corresponding row/column in the master file that reads the same date.

View 2 Replies View Related

Multiple Text Cells In One Cell On Multiple Lines

Jan 23, 2010

share a macro to copy cells from range C20:C300 and paste them in C2 on multiple lines. If possible two lines gap between each cell's value when they are pasted in C2.

View 9 Replies View Related

Copying And Pasting Cells From A Worksheet Into A List Format In Another Worksheet

May 18, 2006

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.

View 9 Replies View Related

Naming Multiple Worksheet From Referenced Cells In One Worksheet

Jan 21, 2013

I have a monitoring database and I want to create a 'source' sheet in sheet 1 whereby when I enter names into a certain column they rename different tabs/sheets in the worksheet. for example, the name 'Brown' inputted in cell 'A2' would rename sheet 2, Black in A3 would rename sheet 3 etc.

View 2 Replies View Related

Copy Data In Non-blank Cells Within Range And Paste Into Cells On Another Worksheet

Jan 19, 2012

I have data in some of the cells within range A26:A39

These cells are populated via an IF function on another worksheet. Even though the cells appear blank (as in the value returned is ""), there is a formula in these cells. I think it's called formula blank?

I am looking for a way to copy the data from the cells within the range which are not blank (ie: not = "") and paste this data elsewhere on the sheet in a list with no blank spaces in between.

I anticipate that there will be 4 non blank cells within this range.

Ideally I would have data from the nonblank cells copied and pasted to cells

View 5 Replies View Related

How Do You Select A Range Of Cells On Another Worksheet Using The Cells Property

Apr 19, 2007

I seem to be going round and round in circles with this, but I'm sure it should be easy.

I'm just trying to select a range of cells in Sheet2 of my workbook.
I've tried many different bits of code, including:

Dim namesTotal As Integer

namesTotal = 2500

ActiveSheet.Range(Cells(1, 1), Cells(namesTotal, 8)).Select

(According to the Microsoft website, this is supposed to be the way to do it?)

View 10 Replies View Related

Copy Cells And Range Of Cells To New Worksheet

Apr 22, 2009

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.

View 9 Replies View Related

All Used Cells On Worksheet

Mar 26, 2008

Sub testexport()
ActiveWorkbook. SaveAs Filename:= _
"C:Documents and SettingsSimonMy DocumentsBook2.csv" _
, FileFormat:=xlCSV, CreateBackup:=False
Application.DisplayAlerts = False
Application.DisplayAlerts = True

End Sub

Sub FindLastCell()
Cells. Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Select
End Sub

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 the range would be from B1the results of the 2nd part)

View 9 Replies View Related

Hiding Cells In Worksheet

Nov 11, 2008

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?

View 3 Replies View Related

Separating Cells In A Worksheet

Jan 2, 2009

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.

View 4 Replies View Related

Move Cells To Another Worksheet

Aug 26, 2008

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

View 14 Replies View Related

Reverencing Cells In Another Worksheet?

Jan 26, 2014

I have two sheets in a workbook.

I'm using this:

='worksheet1'!D2 to get information from the first worksheet into a cell on the second worksheet.

What I want to do is populate a cell on worksheet two with the cell number to reference on worksheet 1. I can't get a formula to be accepted that will reference a cell on worksheet 2 that might say F20 so that it replaces D2 in the formula.

View 2 Replies View Related

Getting Part Of Name Of Cells In A Different Worksheet

Aug 27, 2007

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.


View 9 Replies View Related

Protecting Worksheet But Only Cells

Jul 21, 2006

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:

Sub ProtectSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="password"
End Sub

View 4 Replies View Related

Get Address & Worksheet Name Of Cells

Nov 2, 2006

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.

View 3 Replies View Related

Copy All Cells To Another Worksheet

Aug 28, 2007

I want a worksheet to have alot of data from another worksheet that keeps being updated.

View 3 Replies View Related

Clear All Worksheet Cells

Oct 10, 2007

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)

View 2 Replies View Related

Clear All Cells On Another Worksheet

Feb 22, 2008

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.

' 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.

View 3 Replies View Related

Copy Used Cells To Another Worksheet

Aug 11, 2008

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!

View 3 Replies View Related

Add Data From Userform To Cells On Worksheet

Oct 29, 2011

I have a userform that has 1 combobox and 2 text boxes. i need the two text boxes to go to specific row and a specific column based on the entry placed in the combobox.

I can move data from a userform to a database if i am adding it to the next row, but here, there already is data and i want to add to existing data.

I tried application.match, to find the text in the combobox, but i can't seem to figure out the syntax to add it to column 13 on the same row.

View 5 Replies View Related