|
Automatically bordering in 1500 sheets
Hi! Everybody
I have a workbook with 1500 sheets with same Columns i.e A to M .But the row range is Different some sheets has Range A1 to M2, Some has A1 to M50 and Some has A1 to M20 and so on. Now i want to Bordering the all sheets. Firstly apply the ALL BORDER and then THICK BOX BORDER in all sheets. Is it possible? Because i dont want to do manually one by one. Any Solution will Be most Appriciate Thanks In Advance Hardeep kanwar -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200807/1 |
Automatically bordering in 1500 sheets
maybe you can adapt this:
Sub test() Dim lastcol As Long Dim RealLastRow As Long Dim ws As Worksheets Dim arr As Variant Dim i As Long, z As Long For z = 1 To Worksheets.Count With Worksheets(z) lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column ReDim arr(1 To lastcol) For i = 1 To lastcol arr(i) = .Cells(Rows.Count, i).End(xlUp).Row Next RealLastRow = Application.Max(arr) .Range("A1:" & .Cells(RealLastRow, _ lastcol).Address).BorderAround ColorIndex:=3, Weight:=xlThick End With Next End Sub -- Gary "hardeep via OfficeKB.com" <u44683@uwe wrote in message news:871270dade8c0@uwe... Hi! Everybody I have a workbook with 1500 sheets with same Columns i.e A to M .But the row range is Different some sheets has Range A1 to M2, Some has A1 to M50 and Some has A1 to M20 and so on. Now i want to Bordering the all sheets. Firstly apply the ALL BORDER and then THICK BOX BORDER in all sheets. Is it possible? Because i dont want to do manually one by one. Any Solution will Be most Appriciate Thanks In Advance Hardeep kanwar -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200807/1 |
Automatically bordering in 1500 sheets
Hi Hardeep
Try the below code, Sub Boarding() Dim sh For sh = 1 To Sheets.Count Sheets(sh).Select Range("A1").Select Range("A65536").End(xlUp).Select Range(Selection, Selection.End(xlUp)).Select Range(Selection, Selection.End(xlToRight)).Select 'board Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With Range("A1").Select Next sh End Sub "hardeep via OfficeKB.com" wrote: Hi! Everybody I have a workbook with 1500 sheets with same Columns i.e A to M .But the row range is Different some sheets has Range A1 to M2, Some has A1 to M50 and Some has A1 to M20 and so on. Now i want to Bordering the all sheets. Firstly apply the ALL BORDER and then THICK BOX BORDER in all sheets. Is it possible? Because i dont want to do manually one by one. Any Solution will Be most Appriciate Thanks In Advance Hardeep kanwar -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200807/1 |
Automatically bordering in 1500 sheets
Thanks Sir
It works Fine But not Completely. As I mentioned that First apply the ALL BORDERS and then THICK BOX BORDERS Your Code apply only THICK BOX BORDERS for outer edge ,But not apply ALL BORDERS. Which is apply on inner data. Thanks For your Quick Reply Regards Hardeep kanwar "Ranjit kurian" wrote: Hi Hardeep Try the below code, Sub Boarding() Dim sh For sh = 1 To Sheets.Count Sheets(sh).Select Range("A1").Select Range("A65536").End(xlUp).Select Range(Selection, Selection.End(xlUp)).Select Range(Selection, Selection.End(xlToRight)).Select 'board Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With Range("A1").Select Next sh End Sub "hardeep via OfficeKB.com" wrote: Hi! Everybody I have a workbook with 1500 sheets with same Columns i.e A to M .But the row range is Different some sheets has Range A1 to M2, Some has A1 to M50 and Some has A1 to M20 and so on. Now i want to Bordering the all sheets. Firstly apply the ALL BORDER and then THICK BOX BORDER in all sheets. Is it possible? Because i dont want to do manually one by one. Any Solution will Be most Appriciate Thanks In Advance Hardeep kanwar -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200807/1 |
Automatically bordering in 1500 sheets
Dear Sir
Thanks For Your Code But unfortunataly it does't work when i ran the macro it show the ERROR MASSAGE COMPILE ERRROR: SYNTAX ERROR I am totally stupid in Macros. I knew only how to create and run the macros Thanks hardeep kanwar Gary Keramidas wrote: maybe you can adapt this: Sub test() Dim lastcol As Long Dim RealLastRow As Long Dim ws As Worksheets Dim arr As Variant Dim i As Long, z As Long For z = 1 To Worksheets.Count With Worksheets(z) lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column ReDim arr(1 To lastcol) For i = 1 To lastcol arr(i) = .Cells(Rows.Count, i).End(xlUp).Row Next RealLastRow = Application.Max(arr) .Range("A1:" & .Cells(RealLastRow, _ lastcol).Address).BorderAround ColorIndex:=3, Weight:=xlThick End With Next End Sub Hi! Everybody [quoted text clipped - 11 lines] Hardeep kanwar -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200807/1 |
Automatically bordering in 1500 sheets
Hi Hardeep,
I have changed my code, try it and let me know, if anything else required Sub Boarding() Dim sh For sh = 1 To Sheets.Count Sheets(sh).Select Range("A1").Select Range("A65536").End(xlUp).Select Range(Selection, Selection.End(xlUp)).Select Range(Selection, Selection.End(xlToRight)).Select 'board Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With Range("A1").Select Next sh End Sub "Hardeep_kanwar" wrote: Thanks Sir It works Fine But not Completely. As I mentioned that First apply the ALL BORDERS and then THICK BOX BORDERS Your Code apply only THICK BOX BORDERS for outer edge ,But not apply ALL BORDERS. Which is apply on inner data. Thanks For your Quick Reply Regards Hardeep kanwar "Ranjit kurian" wrote: Hi Hardeep Try the below code, Sub Boarding() Dim sh For sh = 1 To Sheets.Count Sheets(sh).Select Range("A1").Select Range("A65536").End(xlUp).Select Range(Selection, Selection.End(xlUp)).Select Range(Selection, Selection.End(xlToRight)).Select 'board Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With Range("A1").Select Next sh End Sub "hardeep via OfficeKB.com" wrote: Hi! Everybody I have a workbook with 1500 sheets with same Columns i.e A to M .But the row range is Different some sheets has Range A1 to M2, Some has A1 to M50 and Some has A1 to M20 and so on. Now i want to Bordering the all sheets. Firstly apply the ALL BORDER and then THICK BOX BORDER in all sheets. Is it possible? Because i dont want to do manually one by one. Any Solution will Be most Appriciate Thanks In Advance Hardeep kanwar -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200807/1 |
Automatically bordering in 1500 sheets
try this
Sub doborders() For Each ws In ActiveWorkbook.Worksheets lr = ws.Cells.SpecialCells(xlCellTypeLastCell).Row With ws.Range("a1:m" & lr) 'Selection .Borders.LineStyle = xlContinuous .BorderAround Weight:=xlThick 'Medium End With Next ws End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "hardeep via OfficeKB.com" <u44683@uwe wrote in message news:871270dade8c0@uwe... Hi! Everybody I have a workbook with 1500 sheets with same Columns i.e A to M .But the row range is Different some sheets has Range A1 to M2, Some has A1 to M50 and Some has A1 to M20 and so on. Now i want to Bordering the all sheets. Firstly apply the ALL BORDER and then THICK BOX BORDER in all sheets. Is it possible? Because i dont want to do manually one by one. Any Solution will Be most Appriciate Thanks In Advance Hardeep kanwar -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200807/1 |
Automatically bordering in 1500 sheets
Greeeeeeeeeeeeeeeeeeeeeeeeeeeeet
Most Appriciate Thank sir Hardeep kanwar Don Guillett wrote: try this Sub doborders() For Each ws In ActiveWorkbook.Worksheets lr = ws.Cells.SpecialCells(xlCellTypeLastCell).Row With ws.Range("a1:m" & lr) 'Selection .Borders.LineStyle = xlContinuous .BorderAround Weight:=xlThick 'Medium End With Next ws End Sub Hi! Everybody [quoted text clipped - 13 lines] Hardeep kanwar -- Message posted via http://www.officekb.com |
Automatically bordering in 1500 sheets
Its Work Great
Thanks For your Quickly Reply thanks for solving my problem Thank again hardeep kanwar Ranjit kurian wrote: Hi Hardeep, I have changed my code, try it and let me know, if anything else required Sub Boarding() Dim sh For sh = 1 To Sheets.Count Sheets(sh).Select Range("A1").Select Range("A65536").End(xlUp).Select Range(Selection, Selection.End(xlUp)).Select Range(Selection, Selection.End(xlToRight)).Select 'board Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With Range("A1").Select Next sh End Sub Thanks Sir [quoted text clipped - 65 lines] Hardeep kanwar -- Message posted via http://www.officekb.com |
Automatically bordering in 1500 sheets
It could be even better and adaptable to a change to fewer rows IF we knew which column is always the one with the last value. Then it could delete too. -- Don Guillett Microsoft MVP Excel SalesAid Software "hardeep via OfficeKB.com" <u44683@uwe wrote in message news:8717dc00083ee@uwe... Greeeeeeeeeeeeeeeeeeeeeeeeeeeeet Most Appriciate Thank sir Hardeep kanwar Don Guillett wrote: try this Sub doborders() For Each ws In ActiveWorkbook.Worksheets lr = ws.Cells.SpecialCells(xlCellTypeLastCell).Row With ws.Range("a1:m" & lr) 'Selection .Borders.LineStyle = xlContinuous .BorderAround Weight:=xlThick 'Medium End With Next ws End Sub Hi! Everybody [quoted text clipped - 13 lines] Hardeep kanwar -- Message posted via http://www.officekb.com |
Automatically bordering in 1500 sheets
that's basically the route i took in my code, but it wrapped and the op couldn't
figure out how to make i compile. -- Gary "Don Guillett" wrote in message ... It could be even better and adaptable to a change to fewer rows IF we knew which column is always the one with the last value. Then it could delete too. -- Don Guillett Microsoft MVP Excel SalesAid Software "hardeep via OfficeKB.com" <u44683@uwe wrote in message news:8717dc00083ee@uwe... Greeeeeeeeeeeeeeeeeeeeeeeeeeeeet Most Appriciate Thank sir Hardeep kanwar Don Guillett wrote: try this Sub doborders() For Each ws In ActiveWorkbook.Worksheets lr = ws.Cells.SpecialCells(xlCellTypeLastCell).Row With ws.Range("a1:m" & lr) 'Selection .Borders.LineStyle = xlContinuous .BorderAround Weight:=xlThick 'Medium End With Next ws End Sub Hi! Everybody [quoted text clipped - 13 lines] Hardeep kanwar -- Message posted via http://www.officekb.com |
Automatically bordering in 1500 sheets
Sub doborders1() 'assumes A is ALWAYS the longest column
For Each ws In ActiveWorkbook.Worksheets ws.Cells.Borders.LineStyle = xlNone lr = ws.Cells(Rows.Count, "a").End(xlUp).Row With ws.Range("a1:m" & lr) .Borders.LineStyle = xlContinuous .BorderAround Weight:=xlThick 'Medium End With Next ws End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... that's basically the route i took in my code, but it wrapped and the op couldn't figure out how to make i compile. -- Gary "Don Guillett" wrote in message ... It could be even better and adaptable to a change to fewer rows IF we knew which column is always the one with the last value. Then it could delete too. -- Don Guillett Microsoft MVP Excel SalesAid Software "hardeep via OfficeKB.com" <u44683@uwe wrote in message news:8717dc00083ee@uwe... Greeeeeeeeeeeeeeeeeeeeeeeeeeeeet Most Appriciate Thank sir Hardeep kanwar Don Guillett wrote: try this Sub doborders() For Each ws In ActiveWorkbook.Worksheets lr = ws.Cells.SpecialCells(xlCellTypeLastCell).Row With ws.Range("a1:m" & lr) 'Selection .Borders.LineStyle = xlContinuous .BorderAround Weight:=xlThick 'Medium End With Next ws End Sub Hi! Everybody [quoted text clipped - 13 lines] Hardeep kanwar -- Message posted via http://www.officekb.com |
Automatically bordering in 1500 sheets
but i wasn't sure which column had data in the highest row number.. it may have
been M, but i wasn't sure. -- Gary "Tom Ogilvy" wrote in message ... I have a workbook with 1500 sheets with same Columns i.e A to M I thought he said it was always A to M -- Regards, Tom Ogilvy "Gary Keramidas" wrote: that's basically the route i took in my code, but it wrapped and the op couldn't figure out how to make i compile. -- Gary "Don Guillett" wrote in message ... It could be even better and adaptable to a change to fewer rows IF we knew which column is always the one with the last value. Then it could delete too. -- Don Guillett Microsoft MVP Excel SalesAid Software "hardeep via OfficeKB.com" <u44683@uwe wrote in message news:8717dc00083ee@uwe... Greeeeeeeeeeeeeeeeeeeeeeeeeeeeet Most Appriciate Thank sir Hardeep kanwar Don Guillett wrote: try this Sub doborders() For Each ws In ActiveWorkbook.Worksheets lr = ws.Cells.SpecialCells(xlCellTypeLastCell).Row With ws.Range("a1:m" & lr) 'Selection .Borders.LineStyle = xlContinuous .BorderAround Weight:=xlThick 'Medium End With Next ws End Sub Hi! Everybody [quoted text clipped - 13 lines] Hardeep kanwar -- Message posted via http://www.officekb.com |
| All times are GMT +1. The time now is 03:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com