How to draw and clear a border around a cell or range of cells
By Miguel Santos
This FAQ shows you how to draw and clear a border around a cell range. The sample code is in C# but can translated to VB.NET very easily.
This FAQ shows you how to draw and clear a border around a cell range.
The
code assumes that you have defined Excel as:
using Excel=Microsoft.Office.Interop.Excel;
The
code below will draw a thick border around the cells with the default line style
and colour using the range method BorderAround.
public void DrawThickBorderAround(Range cells)
{
cells.BorderAround(Type.Missing, Excel.XlBorderWeight.xlThick, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
}
You can easily make
the code above to draw a border of any thickness bu adding border weigth as parameter.
The
code below will clear any border around the cells using the range
property Borders. Notice that you have to clear all edges and diagonals.
public virtual void ClearThickBorderAround(Range cells)
{
Excel.Borders
borders = cells.Borders;
borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
}
The
sample code is presented in C# but it is easy to translate it to VB.NET.
How to draw and clear a border around a cell or range of cells (3909 Views)
to draw and clear a border around a cell or range of cells This FAQ shows you how to draw and clear a border around a cell range. The sample code is in C# but can translated to how to draw and clear a border around a cell range. The code assumes that you have defined Excel as: using cells with the default line style and colour using the range method BorderAround. public void DrawThickBorderAround( Range cells) { cells.BorderAround( Type . Missing , Excel.XlBorderWeight.xlThick, Excel.XlColorIndex.xlColorIndexAutomatic, Type . Missing below will clear any border around the cells using the range property Borders. Notice that you have to clear all edges and diagonals. public virtual void ClearThickBorderAround( Range cells) { Excel.Borders borders = cells.Borders; borders[Excel.XlBordersIndex.xlDiagonalUp
to create a table of information. I could just make borders in excel however, what I want to do is make it so that this table will also create borders (with a dotted line) for any additionial lines added when Set FSO = Nothing Try with this code Dim rng As Range On Error Resume Next Set rng = Range(Cells(3, 2), Cells(Rows.Count, 2). End (xlUp)).SpecialCells xlCellTypeBlanks) On Error GoTo 0 With rng.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With rng.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With rng.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With rng.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With 'clear dont think that is what OP is looking for. For borders the below single line will do rng.BorderAround xlContinuous, xlMedium Thanks for responding, I tried to input this
to the project. / / Variables protected Excel.Worksheet feuilleExcel; protected Excel.Range Cellules; protected Excel.Range UneCellule; protected Excel.Range DeuxCellules; protected ExcelApplication xls; / / Open worksheet #1 this.feuilleExcel = (Excel this.feuilleExcel.Cells ; / / work on 1 cell this.UneCellule = (Excel.Range)this.Cellules[XListe, YListe]; / / value inserted this.UneCellule.Value = this.ListeTypesCarte[i]; / / cell border this.UneCellule.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1 X, 1] = rs.GetString(0); / / get cells this.DeuxCellules = (Excel.Range)this.feuilleExcel.get_Range(this.Cellules[X, 1], this.Cellules[X, 2]); / / cell border this.DeuxCellules.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1 nomFeuil { get { return (Excel.Worksheet) _ _Excel.ActiveWorkbook.ActiveSheet; } } public Excel.Range Cellules { get { return nomFeuil.Cells; } } public void quit(bool save up worksheet.Activate(); / / Apply the formatting necessary for the report. Range rg = worksheet.get_Range( "A1" , "E1" ); rg.Select(); / / bold rg.Font xlCenter; / / color index rg.Interior.ColorIndex = 6; / / font weight rg.Borders.Weight = 3; / / Border Line style rg.Borders.LineStyle = Excel.Constants
the values across the spread sheet? Thank you. Sub august4macroattempt3_RPJ() Range("A3").Value = "Max" Range("A4").Value = "Min" Range("A5").Value = "Average" Range("A6").Value = "Stddev" Range("A8").Value = "Stddev+Ave Range("A9").Value = "Max" Range("A10").Value = "Ave" Range("A11").Value = "Min" Range("A12").Value = "Stdev-Ave" Dim db As Range Set db = Range(Range("B14"), Range("B" & Rows.Count).End
macro, but it errors out: Sub test() Dim rng As Range, c As Range, cfind As Range, rng1 As Range Worksheets("mail_list2.4_2.5").Activate Set rng = Range(Range("H2"), Range("H2").End(xlDown)) Set rng1 Range(Range("L2"), Range("L2").End(xlDown)) For Each c In rng Set cfind
GoTo NoName On Error GoTo 0 Sheets(" Macro Sheet ").Select Range("DATA").Select Selection.Copy Sheets("FINAL").Select Range("A5").Select ActiveSheet.Paste Sheets("Macro Sheet").Select Range("C4:D4").Select Application.CutCopyMode = False Selection.Copy Sheets("FINAL Select Range("B4").Select ActiveSheet.Paste Range("B5").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = " = 'Macro Sheet'!RC[1] / 'Macro Sheet'!R5C[1]*100" Range("B5").Select ActiveCell.AutoFill Range(ActiveCell, Cells(Rows.Count, _ ActiveCell.Column - 1).End(xlUp Offset(, 1)) Range("C5").Select ActiveCell.FormulaR1C1 = " = 'Macro Sheet'!RC[1] / 'Macro Sheet'!R5C[1]*100" Range("C5").Select ActiveCell.AutoFill Range(ActiveCell, Cells(Rows.Count, _ ActiveCell.Column - 1).End(xlUp
4000-6000 kind of value in sqlserver hi i have range column as nvarchar. . and i have my code is string range = cmbrate.SelectedValue.ToString(); and adding like. . . comm.Parameters.AddWithValue Range", range ); but its atored as 0(zero) only. . how to solve ths is my code. . and column name in databse as Range. . string range = cmbrate.SelectedValue.ToString(); decimal percentage = Convert.ToDecimal(txtpercentage.Text); SqlConnection integrated security = true"); SqlCommand comm = new SqlCommand("insert into marketprofit_morgin(Range, percentage)values(@Range, @percentage)", con); comm.Parameters.AddWithValue("@Range", range ); comm.Parameters.AddWithValue("@percentage", percentage); con.Open(); comm.ExecuteNonQuery(); label10
have thus far. Sub august4macroattemptXX() ' ' august4macroattemptXX Macro ' attempt 3 million ' ' Range("A3").Select ActiveCell.FormulaR1C1 = "Max" Range("A4").Select ActiveCell.FormulaR1C1 = "Min" Range("A5").Select ActiveCell.FormulaR1C1 = "Average" Range("A6").Select ActiveCell.FormulaR1C1 = "Stdev Range("A8").Select ActiveCell.FormulaR1C1 = "Stdev+Ave" Range("A9").Select ActiveCell.FormulaR1C1 = "Max" Range("A10").Select ActiveCell.FormulaR1C1 = "Ave" Range("A11").Select ActiveCell.FormulaR1C1 = "Min" Range("A12").Select ActiveCell.FormulaR1C1 = "Stdev-Ave Range("B3").Select ActiveCell.FormulaR1C1 = " = MAX(R[11]C:R[330