Border Last Range Of Cells

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


ADVERTISEMENT

VBA - To Border Cells With Entry In A Given Range

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

Bordering A Range (draw The Border For The Cells With In The Usedrange From Column A To BM)

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

VBA To Put Border Around A Range

Mar 15, 2013

I am after a bit of code which find a value in a cell "Grand Total" it may not be a the last row

Then finds the last column in the row and puts a border around the whole range.

View 4 Replies View Related

Creating Border For A Range Using R1C1

Mar 6, 2013

I'm trying to create a border for a range using R1C1 and get an error.

VB: Sheet11.Range(Cells(y, 1), Cells(y, 5)).BorderAround Weight:=xlThin

If I do not use R1C1 it works...

VB: Sheet11.Range("A18:E18").BorderAround Weight:=xlThin

View 2 Replies View Related

Border Around Range To Form Shape

Apr 12, 2008

For my university coursework I've created a comprehensive building cost model but I'm struggling with one aspect, copying the plotted ground floor footprint to enable floor finishes to be selected. A user can plot shapes in a 50x50 grid (B2:AY51). The shapes are displayed using conditional formatting. All shapes are conditionally formatted red, i.e. colour index 3. I wish to place a border around the plotted shapes, so that I can then copy the outline to a new tab. e.g. Range B2:AY51, If cell conditonally formatted red then place borders to the sides of the that cell that share a boundary with a non-conditonally formatted red cell.

View 3 Replies View Related

Border Around Cells With Same Value

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

Conditional Formatting To Change Outside Border Of Range Only?

Oct 24, 2013

I'm trying to use conditional formatting to colour the border of a range of cells when another cell has data in it.

I can get it to color all the cells borders within that range when there's data in that cell but I just want the outside of the entire range.

View 4 Replies View Related

Copy Range In Another Sheet And Create Border

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

Using VBA To Add Border To Bottom Of Certain Cells

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

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

How To Add Thin Style Border To Cells

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

VBA To Add / Remove Border Around Merged Cells

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

VBA - Putting A Border Around Cells That Arent Empty

Nov 3, 2008

I am trying to Put a border around cells that arent empty. Is this possible?

View 11 Replies View Related

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 View Related

Double-Click Cell Border Jumps To Other Cells

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

Excel 2003 :: VBA Macro - How To Prevent Cell Border Changes To Unprotected Cells

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

Formula To Average Range Of Cells And Copying It To Calculate Next Range Of Cells?

Mar 17, 2014

I have a row data corresponding to the measuring of load cell per min and I need to average the values per hour. So I have a column B for the date (from 1-01-2013 to 31-01-2013, column C for the time (0:00:00 to 23:59:00), and D de values per min I want to average. I have to do the same for the rest of the month of 2013 (February, ...., December).

I would like to know if there is a way to create a formula to calculate the average of the first 60 values (to get the average of the first hour of 1-01-2013), and then copy it to get the average of the following 60 (average of the second hour in 1-01-2013) and so on.

If there is no way to do it, I would like to know if I can do it using functions like average, match, index, offset, what would be the best match of those functions.

I also tried it by doing the analysis in another tab and using the function "averageifs" with two criterias: one for the date (example 01-01-2013) and another one for the hour (example 0:00:00), but it didnt work, it show error: #value. I inserted an extra column in the data tab with just the hour (example 0:00:00) in front of the corresponding column with (example 0:01:00, 00:02:00, etc)
Equation I used for this:

=AVERAGEIFS('Data (min)'!D$6:D$43206,'Data (min)'!$A$6:$A$44646,A6,'Data (min)'!$B$6:$B$44646,B6)
=AVERAGEIFS(TAB AND COLUMN WHERE THE RAW DATA IS,RANGE OF CRITERIA 1,CRITERIA 1,RANGE CRITERIA 2,CRITERIA 2)

View 2 Replies View Related

VBA Code To Add A Border

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

Border Around Duplicates

Jan 29, 2009

I have a list (approx 2000 rows). I'd like to put a seperate border around any duplicates that are in the fourth column. If there isn't a duplicate then the regular gridlines are fine. I'd only like the first four columns to include this border. Is there a way for excel to automatically place borders around duplicates? Here is an example of what I have and what I would like it to look like.

View 9 Replies View Related

VBA To Know What Kind Of Border It Is

Mar 28, 2009

Does any know of excel code to know what know what kind of border it is. I converted a pdf file and now the data are on excel and there are borders and it looks to me like it is border with thickness of medium but when I run a code I produced and mark X to the column next to it to show that it is that border it gave me X on all the rows and so the medium thick border is excel grid without any borders.

View 9 Replies View Related

Add Border Around Selection

Aug 24, 2007

There was also another bit of code which added a border around the selected text.
It got stuck on this:

With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

The problem was that there was no Inside Horizontal if there was only one row. I had to chage my code to include a header as well as any selected rows - giving me the two row minimum required. This does the job for now - I'll have to give it some thought on how to get around the problem without the extra header though.

View 4 Replies View Related

Cell Border Non-visible?

Jan 7, 2009

I have seen spreadsheet where the user has made the outline of the cell border non-visible. This seems like a fairly simple task, but I can't seem to find out how you format this type of border.

View 2 Replies View Related

Xl 07 Can't Change Border Colour

Jan 16, 2009

I am using Excel 2007. One of my worksheets has a dark border around some cells. Instead of being blue the lines are black. I used the right click and format cells option but it does not work.

View 10 Replies View Related

Border In Each Page Break

Jun 9, 2009

Bottom border each page,

View 9 Replies View Related

Cell Border Disappearance

Dec 19, 2009

When I enter data (digits) into a cell, its left border
becomes thinner ! Where is the connection between format and content ?

View 14 Replies View Related

Border Formatting In Excel

Nov 3, 2013

Creating border in different size at single attempt. Example as shown in the picture.

Excel Format.jpg

View 1 Replies View Related

How To Change Type Of Border

Apr 24, 2014

How can I modify this code:

HTML Code: 

Sub aaanewa()
Dim lRow As Long, c As Range
lRow = Cells(Rows.Count, "D").End(xlUp).Row
For Each c In Range("D1:D" & lRow)
If c.Value > "" Then

[Code]...

To include this:

HTML Code: 

With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = -0.14996795556505
.Weight = xlThin
End With

so i can change the color and size of border

View 2 Replies View Related

Removing Border Of Group Box

Nov 20, 2008

I've used Group Boxes to isolate some Option Buttons. I would like to remove the line border of the Group Box, but I don't see a way to format it.

View 2 Replies View Related

Draw A Border Using Line?

Aug 14, 2013

I'm trying to use VBA to draw a border using the drawing toolbar and using the line. i need it to draw a border around a select area i hightlight.

i have attached a example below, but not sure if it will appear.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved