VBA Code To Create Border Around Used Cells In Worksheet
Nov 20, 2013
I'm writing/recording a macro and would like some VBA code. I'm trying to make a border around all used cells in my sheet. When I use the macro there will be a variable number of used cells.
I'd like code that says:
If cell A2 has text then format cells A2:CL2 with an outline border Repeat for each row in the sheet until there there is no more text in column A.
Also, not sure if it matters, but I'm working on a mac.
View 9 Replies
ADVERTISEMENT
Sep 5, 2013
I don't know VBA code but i am familiar enough to know that a code can be written to separate out information into different tabs on a worksheet.
For example, I have a standard worksheet with say 20 columns going across and 20,000 rows going down. At each "name" change in row 1, I would like a macro to copy into a new tab. Basically the end result will have a tab for each name and the corresponding columns that go with the name.
View 2 Replies
View Related
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
Feb 14, 2009
to copy column C and D, from row 3 to the end, from sheet2,in sheet1, column A and B, starting with row 2 and after that I need to create a border to the copied values and I need to create a border to the empty cell from column C.
open attached file
View 14 Replies
View Related
Jul 26, 2009
I have recorded a macro which will create the border for me when the file is opened, but it turned out too long. if someone can show me how to reduce the codeing. I have the attached file.
Range A5 to E20 thin Border all sides and thick border allround (16 rows)
Range A21 to E36 thin border all sides and thick border allround (16 rows)
end range is A356.
View 5 Replies
View Related
Oct 4, 2011
The below code adds a line to the next blank row. I would like to add a border to this row that the values are going into. Columns "A:G"
Code:
Private Sub CMD_Add_Click()
Dim rNextCl As Range
Set rNextCl = Worksheets("Main").Cells(Rows.Count, 2).End(xlUp).Offset(2, 0)
Worksheets("Main").Activate
rNextCl.Select
Dim iRow As Long
[Code]....
View 9 Replies
View Related
Nov 6, 2007
part of my code looks like this. Instead of it being if c.interior.colorindex i want it to be if top border color = 4.
How do i syntax this?
For Each c In Worksheets("Sheet1").Range("B7:DE7").Cells
If c.Interior.ColorIndex = 4 Then Range("DH7").Value = Range("DH7").Value + c.Value
Also, is there a way to specify Range("DH") and make the row be the current row, not hard coded to 7
View 9 Replies
View Related
Jun 1, 2008
I have an excel sheet that is pulling data from other sheet based on some cretaria...i want a macro that when ran on this excel fixes column width , makes cell border as "dotted" and outline the area with Thick Outline.
This is what a macro code should do ; Hope this makes sense...thanks.
1. Finds numeric values in rows falling in colum B,C,D,E , than border those cells as dotted (i.e. right click on cell - format column - Border - Style - 3rs on top left row).
2. After filling cell border style as mentioned above , macro should do a THICK BOX BORDER around a cells falling above dotted cells.. i.e. one big sqaure outlined contaiing dotted cells with numeric values.
3. Trick part , my sheet is divided into 2 parts , INTERNAL and EXTERNAL. So i have writen "INTERNAL" in A1 cell and EXTERNAL in 'A' row in last row after INTERNAL lines. So the THICK BOX BORDER as requested in pt 2 shud be separted ones for INTERNAL and sep from EXTERNAL.
View 14 Replies
View Related
Aug 13, 2014
Is it possible to modify the attached code so that it will copy bold text and border as shown in attachment sample1 and paste in sheet Shop. Currently the code just copy's and pastes without bold text and borders.
Sample1.PNG
View 4 Replies
View Related
Oct 15, 2012
Iam looking for macro to copy rows based on partial cell content of a column. I have an excel spreadsheet called "arc.xlsx" from which I would like to copy data to other few new excel files when certain criteria are met. The excel file contained location is C:Documents and SettingsxxxxDesktopCompany.
Below is a sample of arc.xlsx
GP CUST_NO BR CUST_NAME day mo year
I1 999999 1 SMITH 0 8 9
I1 999999 ab SMITH 4 8 9
I1 999999 cd SMITH 4 10 9
I1 999999 1 SMITH 4 1 10
[Code]...
I would like the macro to copy rows that have 'ab' in the column c (with title BR)and save it in a new excel file with name ab.xlsx in the same location folder.And the same for 'cd', '01' and '02' by saving the data in files with name cd.xlsx, 01.xlsx so on.
View 3 Replies
View Related
Mar 23, 2006
Below a small example with the borders as they should be. How do i get a macro to put the borders automatically, depending on the similarity of the cells in column B ? Note the blanc column with the borders should be made automatically as well. Second problem: the minimum height of a "border" should be 34pixels as in the example so you can write something in there after printing the sheet.
View 9 Replies
View Related
Jul 19, 2012
I need to underline certain cells based on the value of a cell in this case from column "E". Right now I run a macro to look at the value in each cell in column "E". THe macro then adds a certain amount of blank rows below the that row. So, if cell E1 has a 4, my macro runs a calculation that says 4 divided by 3, +2. So it would add 3 blank rows under row 1. It does this all the way down my column of data.
Now it gets tricky. To the right of column E, I have 6 blank columns, F,G,H,I,J,K. F,H, and J are spacer rows with no data. I need them blank with out any borders. Columns G, I, and K are the columns that I am wanting to add borders on the bottom of the cells based on the value in column E.
I need code to look at the value in E1, in this case 4, and add borders to 4 cells, in this case G1, I1, and K1 and then G2. If the value would have been 5, I need I2, 6 would add a border to K2 and 7 would add a border to G3. Catch my drift?
So, every cell in column E will not have a value because of me adding blank rows. So when there is a value, I need to add borders starting on the same row the value is in. At most, there will only be 3 underlined cells per row because of the format. Once the top row is filled, I need to drop down to the very next row.
View 9 Replies
View Related
Aug 3, 2008
I am trying to have a border drawn around a group of cells. This group of cells changes per list I make, but only the height changes. The width is always the same (from column A to column J) and the top is always at row 5. Only the last row changes. How can a border be drawn around this group of cells no matter the size of the group of cells? Below is the macro I have written so far.
Cells.Select
Cells.EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=7
Columns("K:N").Select
Selection.ClearContents
ActiveWindow.SmallScroll ToRight:=-7
Range("A2").Select
Rows("2:2").RowHeight = 49.5
lastrow1 = Range("A65536").End(xlUp).Select
Range("A5:J" & lastrow1).BorderAround.Weight = xlThin
lastrow2 = Cells(Cells.Rows.Count, "C").End(xlUp).Row
For I = lastrow2 To 2 Step -1..........................
View 2 Replies
View Related
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
Mar 2, 2012
I recorded this and I was wondering if there was a more simple way to add a thin style border to my cells?
'Insert BorderUnion(Range("K6", Cells(LastRow, "K")),
Range("B6", Cells(LastRow, "B"))).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
[Code] ......
View 3 Replies
View Related
Oct 5, 2012
I've got some VBA that adds and removes a border around a merged cell. I created it by recording a macro and it's pretty clunky. It needs to do this for 6 different merged cells, based on different events and takes around 5 seconds to complete. Not a major problem, but I was hoping that I could make it more efficient. Anyway, code below:
Adding the border:
Code:
Sub AddRAGBorderChart1()
'
' AddRAGBorderChart1 Macro
'
'
Range("D4:M15").Select
[code]....
Removing the border:
Code:
Sub RemoveRAGBorderChart1()
'
' RemoveRAGBorderChart1 Macro
'
'
Range("D4:M15").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
[code]....
View 6 Replies
View Related
Feb 28, 2007
Using VBA how could I put a border around any cell that isn't empty within the range B1:C500. (Worksheet name - Floc BOM)
View 9 Replies
View Related
Nov 3, 2008
I am trying to Put a border around cells that arent empty. Is this possible?
View 11 Replies
View Related
Aug 28, 2013
know the VBA script to select only specific cells within a worksheet. To be more precise, on sheet 1 - A2, A6 and A10 cells are filled with red color. I want only those cells which are highlighted in red to be selected, copied and pasted to sheet2.
View 3 Replies
View Related
Oct 23, 2008
I'm trying to double-click a cell to edit the contents, and I am off a few pixels, it treats the double-click as a double-click on the cell border, not the cell contents, and the focus jumps to another cell in the direction of the border instead of going into edit mode. The effect is as if I had pressed Ctrl and the arrow in the direction of the border I clicked on.
The only way I've found to disable this is to turn off the option "Enable fill handle and cell drag-and-drop", but unchecking that also disables actually useful things, like dragging the corner of a formula call to copy it down, etc.
Is there a good way to disable this cell jumping when I accidentally double-click a cell border?
View 9 Replies
View Related
Sep 21, 2009
I would like to draw the border for the cells with in the usedrange from column A to BM.
I have the below code, that I was using to border the column D alone.
When i change the range from "A:BM", i dont get the intended output.
Can somebody tell, what modification, I should do to get the desired output?
View 5 Replies
View Related
Aug 3, 2012
I have unprotected cells in excel where I need to change the content (number) and cell color, but I don't want the cell borders to change. I'm a beginner with vba. I've tried protect sheet and workbook options, but looks like I need a vba code and don't know where to start.
Win 7
Office 2007
View 4 Replies
View Related
Sep 2, 2013
The new worksheet is created to the left of the existing source worksheet.
View 2 Replies
View Related
Feb 11, 2009
I have code in a worksheet that creates a new worksheet when clicking a button:
View 3 Replies
View Related
Jan 14, 2009
So I've got Sheet 1 with say
____A___B___C
1___m___i___c
2___r___o___s
3___o___f___t
I would like to create a button that can create a new sheet and paste A1 to C3 at the same location on the new sheet
and I need this to create a new sheet and do that everytime the button is pressed.....
View 11 Replies
View Related
Feb 22, 2008
I'm trying to add buttons to an excel spreadsheet at runtime. Each row in the spreadsheet should have its own buttons. I was able to create and edit them with the ActiveSheet. OLEObjects.Add() function, but after that, when i was trying to create code dynamically to react on the buttons' click events excel crashes (actually it works for one button, but not if my routine for adding a new button and event code is called more than once in a row!)
The code below works if the AddCmdbuttonWithCode() is called once, but crashes if it is called two or more times. Excel tries to restore the document after the crash and the first button and its corresponding click event code is visible and works... but NOT the second button and its event code...
The only way I can create multiple buttons right now is by calling my method once per click, opening the vba editor, changing the parameters for my AddCmdbuttonWithCode() routine and execute it again. After that I have mutliple buttons in different lines which all work fine (so the concept seems to work).
The problem seems to be the insertLine method, since everything seems to work if i leave it out (except for the code generation of course, since this is done by this part of the code :-) ). Is it possible that calling the insertLine Method can't be called multiple times? I don't know... any ideas? Feel free to test my code - it's small, easy to understand and has comments.
'this code calls the method which creates the buttons in specific cells with specific names
Private Sub CommandButton3_Click()
'the first call always works!
AddCmdbuttonWithCode "Edit_111_111_114", 23
'the second one crashes excel
AddCmdbuttonWithCode "Edit_111_111_115", 27
End Sub
View 3 Replies
View Related
Feb 23, 2014
I am copying data from worksheet "Microsoft" to another workbook and paste in sheet1, i want the cell G1 to auto input the worksheet name "Microsoft" where i copy the data from,
How to have G1 show the worksheet name after i copy and paste the data from worksheet name "Microsoft".
View 6 Replies
View Related
Dec 5, 2012
I am trying to write some code that will check a range of names within a worksheet and if there is a sheet with a name from the range excel should ignore it and move on to the next range. If no such worksheet exists it should create it. When the code encounters a blank range, it should stop the code.
I have tried several different variations, and either I can't make the "check if exist" statement to work, or I am having problems with it not working for more than one loop. After reading previous posts on this forum, I have tried with err.clear, next ws in worksheet and all types of codes but I can not make it work.
When I am running the code, it will stop on second loop at [If (Worksheets(rangename).Name "") Then] and give me a runtime error 9 - subscript out of range.
This is the code:
Sub CreateSections()
Dim i As Integer
Dim rangename As String
Dim Newsheet As String
Dim Nextrow As String
Application.ScreenUpdating = False
Sheets("Example").visible = True
[Code] .........
View 2 Replies
View Related
May 17, 2006
I have a vba module that reads a value from a txt file and saves that value in a variable called "iniInfo". I want to permanently assign that value programatically by using VBA code to create a Public variable e.g. "PUBLIC CONST conFIg = iniInfo" in my modGlobalVar Module. (Of course, this would only create the code if the "conFig" variable doesn't already exist.)
View 2 Replies
View Related
Mar 6, 2014
Create a search engine in vba for a worksheet, a macro to be assigned to a button. The 'search engine' to that 'Find button' that I have in mind is on these parameters:
- search any result, complete or partial, non-case sensitive;
- the text to search is typed in B2;
- after the text to search is typed, it only works by pressing that 'Find button';
- it only searches column D in the current worksheet;
- tells the total number of matches found in C2;
- when that Find button is clicked, it goes automatically to the first result found and stops;
- if that Find button is clicked again then it goes to the next found match and stops, and so on;
- when it reaches the end of the document, it just restarts to search from the beginning;
- if there are no matches, it just makes a sound and does nothing; and types zero (0) in C2.
View 11 Replies
View Related
