I have code that works well to insert a blank row. I need to remove all vertical lines each time it adds a row. You can see I commented out the line I tried.
Sub InsertRow()
Dim LstRw As Long, ChkRw As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For ChkRw = LstRw To 6 Step -1
If Cells(ChkRw, "A") Cells(ChkRw - 1, "A") Then
Rows(ChkRw).EntireRow.Insert
'Selection.Borders(xlInsideVertical).LineStyle = xlNone
End If
Next ChkRw
I'm trying to create an area chart to show the change in the number of staff working in my store over time. Unfortunately, Excel likes to draw diagonal lines. That is if I have the following data:
Excel will draw a diagonal line between each data point, rather than creating a box. This gives the illusion that there are somehow half people working at 9:30 and 10:30. Is there a workaround?
I am having a problem formatting a chart in Excel 2010. My chart has multi-level category axis labels, and I would like to have a vertical grid line separating each major group of categories. In Excel 2003, I could right-click on one of the gridlines and then specify the spacing I wanted between gridlines. In Excel 2010, as soon as I indicate that I want multi-level category axis labels, I get a vertical gridline between each category and I am unable to alter the spacing. If I deselect the multi-level axis label option, I can adjust the spacing between the vertical gridlines, but the axis multi-level label functionality is lost. Is there a way to fix this problem without having to resort to using the drawing tools or text boxes to achieve the desired results.
In column CT between rows 11:210 is the vertical data that includes duplicates. I am looking to create a formula that can lookup each value between CT11:CT210 and return only the unique values horizontally starting in cell CW9 and onwards.
Example: CT11:CT14 looks like 5x20 6x4 5x20 5x8 and so on and so forth..
I would then like this formula to return the above data (which goes all the way to CT210) in this format starting in cell CW9 - 5x20 then CX9 - 6x4 and finally CY9 - 5x8.
1. Is where the user is putting in data. 2. Is a spreadsheet with the data choosen from the action of the user, but it is containing also the non-choosen data from the input spreadsheet. 3. IS how I want the data shown to me either automatic or thru a macro. So is there any code like IF or some Macro who would make this happen?
What I want at the end of the day is a list of products that the user have put in and nothing else, with the QTY and price and the sum of that.
Here is a picture example of what I mean, just randomly found on google: http://www.projectwoman.com/uploaded...ent-763577.jpg
Maybe an insignificant thing I want, but can the line from the comment connecting to the red indicator be removed? Added an example since my comments show up below the selected cell with VBA code.
I am designing a from with a variable number of titles. The user should be able to insert a title into a column by typing in the title that he wants into a text box, and pressing a command button that I have created ( named add). The problem arises when I try to get the text to lie vertically as opposed to horizontally (in the cell, not as a text box. If this can only be done as a textbox, let me know). Is there any way to do this?
In Sheet1 cell A1 has concatenate formula and the data source is linked from sheet "DataInput". Sheet1 cell A1 has multiple data and there are blank lines within the cell. I would like to remove these lines so it looks clean without any gaps. So basically its looking like this right now
Harry
Andrew Joe Apple
Billy
I want it to look like this but i am having a difficult time because this cell contains a formula
Harry Andrew Joe Apple Billy
A formula or something that would fix this problem automatically.
I am trying to write a formula to insert in a macro for multiple lines of data.
Column B contains the date that I am comparing the date in Column C against. I want to be able to highlight the content on that row if the date in column C is greater than or equal to column B. how to write this?
I would need row 1 and 4 to highlight in red. this is part of a long Macro that is written and includes many other steps, but I cannot seem to make this step work correctly.
I have a worksheet that contains data arranged in fields from columns A to J. The relevant columns for the purposes of the macro are columns B (customer codes) and G (sales values). Column B may contain a single instance of a customer code, or multiple (over 50), depending on how active a customer has been. The worksheet always contains many different customer codes with varying numbers of rows for each customer, sorted by customer. What I'm trying to do is write a macro that will loop through the worksheet and insert a blank line immediately after a change in value of column B (customer code), and in this blank line, insert the customer code in column B (which comes from the cell immediately above), a sum formula for all values within a range that relate to that particular customer in column G, and an IF statement in column J that relates to the SUM formula. The loop concludes when there are no longer values in column B.
I have managed to insert blank lines on change in values in column B, but am not sure about the best approach for inserting the SUM formula or the IF statement in columns G and J respectively.
I'm looking for a way to write a macro to insert 5 lines at the end of the data in column A. Then I want to copy a range into the newly inserted lines. I would press a button anytime I need this to occur.
Current last line of data A39
Need to insert 5 rows after A39
Then copy range BA30:CB34 into the newly inserted rows.
I am brand new and I don't know a ton about macros and programming in Excel.
What I'm trying to do here in this attached spreadsheet is at the end of inputting my line, I want to hit the tab (marked in red to make it clear where) button in the cell and have it automatically create another blank line but keep the formulas the same.
Basically I just want to avoid having to right click and insert line and then drag the formulas column by column down which is difficult.
See attached : TEST2_2014 Sales & Commission Spreadsheet.xlsx‎
I have two vertical ranges that I need summarized into 2 adjacent vertical ranges.
" A B C D | SUMMARY model qty| modelqty 1 4.12922.0000| 4.12952.2000 2 2.000012.1250| 2.000025.1250 3 4.12929.0000| 318.0000 4 318.0000| 5 4.1291.2000| 6 213.0000| "
A1:A6 is my SKU's model number B1:B6 is my inventory C1:C6 should contain formulas that result in a summary of the models D1:D6 should contain formulas that result in a sum of the inventory count for each model
how do you create a graph with a field such as vendors on the X-axis, but with dollar values spent on the left side of the y-axis and percentage of total dollars spent on the right side vertical axis?
Got a slight problem; I am making a "worker-plan", where you should be able to write a name on one file and have it updated (removed) from the equivalent columns on another file automatically. Here's an example with two separate projects:
Project 1 before and after changes: Attachment 257366 Attachment 257367
Project 2 before and after changes: Attachment 257368 Attachment 257369
You can see here how I intended it to work. When one worker is moved from Project 1 to Project 2, that worker should also be removed from those weeks in Project 1. The previous worker from Project 2 should also be removed from those weeks (in Project 2).
Are there any formulas that can make this work automatically? All week numbers will belong to the same columns in all files.
Edit: There are 30 files that should be syncronized, not just two.. I could also gather all files into one file with sheets, instead of having seperate files.
I have found a very useful UDF for removing non-alpha characters from strings. (See below, Credit for posting to Stanley D Grom - Ozgrid post ´Removing Non-alpha Characters From Text´).
Option Explicit
Private Function RemoveCharacters(InString As String) As String Dim intLoopCounter As Integer Dim intStringLength As Integer Dim intASCIIVal As Integer intStringLength = Len(InString) InString = LCase(InString) For intLoopCounter = 1 To intStringLength intASCIIVal = Asc(Mid(InString, intLoopCounter, 1)) If intASCIIVal >= 97 And intASCIIVal <= 122 Then RemoveCharacters = RemoveCharacters + Mid(InString, intLoopCounter, 1) End If Next intLoopCounter End Function
Two requests:
1. Could the UDF be modified such that any part of a string contained within brackets is also removed (e.g. "NLGA High Street (West-Enfield), EN6" becomes "nlgahighstreeten")?
2. Can an argument be added to the format of the UDF, such that numbers (0 to 9) are either included or excluded (e.g. RemoveCharacters(A1,1) where the argument ´1´ would include any numbers (0 to 9), so "NLGA2003 High Street (West-Enfield), EN6" becomes "nlga2003highstreeten6")? ´blank´or ´0´would exclude these numbers, i.e. would return "nlgahighstreeten"
Trying to build a form that helps assemble parts a pieces based on filling out just a small section of information. Here just a short example:
Tower Type: Type 1 Coax Length: 100 ft
Based on the above criteria, I am going to have a line item that auto fills specific parts and pieces based on the answer. The main problem I have is the coax length answer will fit within several ranges and I need a cell to be able to identify and autofill ALL of those ranges.
Specifically, if the coax length is between 0 and 100, I need to autofill the below cell with Coax 1, from 101 to 200 Coax 2, and so on and so forth. Lets say I have 5 ranges of "greater than, less than" values. how do I make a formula that will consider all of these in a VLOOKUP scenario?
If I haven't asked the right questions, or phrased them incorrectly, please let me know....I'm not the most excel experienced person...hence my visit to this forum!
I have a master data sheet with four columns, A, B, C and D
Column A has the primary data and B,C,D has dependent data values;
So when I insert a new cell in Column A with cells Shift Down, I want mandatorily new cells to be inserted in the same row in col B, C and D as well so that data integrity is maintained;
My spreadsheet has 459 rows; however, the vertical scroll bar ends at row 569738. I have attempted to delete all of the extra rows by holding SHIFT and CTRL, striking the down arrow then right-clicking on the left margin of highlighted row numbers and choosing delete. I have also done the same procedure except clicking clear all from the editing menu. None of these methods has removed the rows and enlarged the vertical scroll bar to a proportionate size in relation to the number of rows.
I have a worksheet which find a cell in a sheet using VBA and then print the value of this cell using the range.printout method.
Works great but now I want to print 2 cells (let's say A1 & B1) but I want the values to be printed below each other instead of next to each other.
So A1 should be printed on the first line and B1 on the second line. When I use Range(A1:B1).printout the values are on 1 line. How can I let them print each value on a new line ???
