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


ADVERTISEMENT

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

Macro To Draw The Cell Border

Sep 30, 2009

Since I did not know how to do this I used the record macro cheating option.

Excel spit out this chunck of

View 6 Replies View Related

Macro To Draw Border And Set Formatting In Pivot Table

Apr 13, 2012

I came across an issue on the pivot table after refreshing data. I always need to manually redo the border and formatting. I figure that it is because every time when some new group have move to another day, it change the pivot table layout again and so on.

1) I manage to draw border for Day 1, 2, 3, 4 and 5 but 'Beyond Day 5' i dont know how to do it.
2) Sometime some Day X will have no data then i will have problem with my script. (example pivot table doesnt show Day 2)
3) Possible to do looping for that?

I had attached a simple file.

View 6 Replies View Related

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

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

Draw An Arrow Equal The Size Of The Selected Cells

Sep 16, 2008

I want to select some cells (like A2-A20) and have it draw an arrow down the selected cells. Tried a few different things, none of which worked...

View 14 Replies View Related

Format Random Number Cells As Draw Result Entered

Nov 17, 2008

Format Random Number Cells As Draw Result Entered ...

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

Usedrange Not Refreshing

Oct 12, 2007

I have a worksheet on which i use vba Usedrange to determine all the cells that are being used, but it only picks up all the range after the worksheet has been saved. It seems that the "usedrange" property only holds the actual usedrange at the last time the workbook was saved.

Is this correct?

If so, is there another way to "refresh" the usedrange property without saving the workbook.

If not, is there another way I can determine what the usedrange actually is at any point in time?

View 9 Replies View Related

Looping Through Usedrange

Jan 10, 2010

I have a usedrange in a workbook, say a1:g150 - simple.

I want to loop through just the first row of this usedrange and delete any rows that are empty.

Now I am sure this is simple and i just cannot get my head working. I have managed to select the first row and loop through, but I dont want to 'select' I just want to loop.

How can i assign the range to a variable? I can get the number of rows via usedrange.rows.count, which would return say 150, but how do i then assign a new variable the range A1:A150? then I can loop by using "for each cell in newrange"?

View 9 Replies View Related

Usedrange Property

Apr 22, 2007

macros in excel XP.

I written up a marco code to do series of calculations for me. I have one program that will use different matrix size (say 5 rows by 5 colums for one set of calcuations and open up another, but with 105 rows and 35 columns)

Now,
the way it works is the user input data in row 1 and column 1 and the marco does the following codes-

Dim numrows As Integer
Dim numcols As Integer

numrows=worksheets("template").usedrange.rows.count
numcols=worksheets("template").usedrange.columsn.count

thisworkbook. names.add "matrix",,,,,,,,"=!r2c2:r" & numrows & "c" & numcols

this code defines my range that will be used in the formulas further down the code.

the problem is that I test ran the code using the matrix=b2:ai105
and I was using the code for a run with matrix=b2:q77

but the code is giving me the range from the previous macro run (ie. b2:ai105) and want it to use the matrix based on new data size (say b2:q77).

I tried using activesheet.usedrange into the macro, but it does not work.

Basically I need to "reset" usedrange property so it uses the correct range every time.

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

Cells.Rows.End(xlUp).Count - Insted Of - UsedRange.Rows.Count

Mar 24, 2008

I used Sheets(1).Cells(1, 1).Rows.End(xlUp).Count instead of UsedRange.Rows.Count in this code , but it didn't succed with me. Why and how to do that


Dim i As Long, j As Long
j = 1
For i = 1 To UsedRange.Rows.Count
Sheets(2).Cells(j, "a").Value = Sheets(1).Cells(i, "a").Value
Sheets(2).Cells(j, "b").Value = Sheets(1).Cells(i, "b").Value
Sheets(2).Cells(j, "c").Value = Sheets(1).Cells(i, "c").Value
j = j + 1
Next i
End Sub

View 9 Replies View Related

UsedRange.Offset Is Erroring

Apr 12, 2007

I am getting an 1004 error:

Application=defined or object-defined error here:

ws3.UsedRange.Offset(1, 0).ClearContents

Relevant
Sub Importer()
Dim LastRow As Long, LastRowSrc As Long, LastRowDst As Long, i As Long
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, C As Range
Dim rng1 As Range, rng2 As Range
Dim sPath As String

Application.ScreenUpdating = False

sPath = "C:Documents and SettingsDougDesktopTGSTGSFiles"

View 9 Replies View Related

Breaking Links And UsedRange

Jun 23, 2006

I am working on a project that involves copying data from one sheet to another, then breaking all links to the original sheet. This part of the program is working fine. The problem comes when I try to put borders around the used range of cells in the new sheet. I want the border to only go down to the last row that contains text in column A.

Currently, the program is putting a border around all the cells that have text plus those that originally contained links (all the links are not always used). So even though I have removed the links from the new sheet, usedrange still includes these extra rows. This is no good for printing, since the links spill over to page two and I dont want that second page to print if there is no data.

So I basically need to know how to fix the problem with usedrange, or instead need a way of specifying the last row that contains text in column A. I have done a search on this and tried a number of different things, however none have worked so far.

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

Find Unique Value And Paste With Content From Column With Border

Aug 17, 2009

My goal is following:

I do an inventory of my schools equipment and have much info in many columns. But I just want to concentrate on column A.

This is example of the room names at my school and look like this:

Column A
A01
A01
A02
A02
....

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

Find Specific Phrase In A Column And Change The Row Color And Its Border

Mar 27, 2014

I have the table, as shown on left in the appended image, and the final result should be the right one.

The steps are written as 1, 2, 3 and 4.

Untitled.png

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







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