Microsoft Excel - Create cell bordres for occupied cells using VBA

Asked By Darnell
09-Feb-11 09:20 AM
I have the following code below 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 I re-run the macro.  Any suggestions?

Set FSO = CreateObject("Scripting.FileSystemObject")
 
    Set Folder = FSO.GetFolder(foldername)
    Destination = Folder.Name
    Destination.IndentLevel = Level
 
    Destination.Offset(0, 1) = Folder.Size / 1000000
    Destination.Offset(0, 2) = Folder.Files.Count
    Destination.Offset(0, 3) = Folder.Path

   
    Set Destination = Destination.Offset(1, 0)
 
    For Each SubFolder In Folder.SubFolders
     ListFoldersAndInfo Folder.Path & "\" & SubFolder.Name, Destination, Level + 1
    Next SubFolder
 
    Set FSO = Nothing

  Anoop S replied to Darnell
09-Feb-11 09:41 AM
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 the variable
  Set rng = Nothing
  Jackpot . replied to Anoop S
09-Feb-11 11:28 AM
Hi Anoop

I dont think that is what OP is looking for. For borders the below single line will do

rng.BorderAround xlContinuous, xlMedium
  Darnell replied to Jackpot .
09-Feb-11 11:39 AM
Thanks for responding,  I tried to input this but got an error.  How and where would I incorporqated this into my code?
  Jackpot . replied to Darnell
09-Feb-11 11:45 AM
Hi Darnell

That was a response to Anoops post.

How do you think adding a border is going to help. What if some folders are deleted. 
  Darnell replied to Jackpot .
09-Feb-11 11:59 AM
Adding borders around the cells serves to create some seperation between the different rows and columns.  Its a little annoying to look at if they are not separated.  As for the the deletion of folders.  This is a photo folder with many different subfolders.  I am constantly adding new folders so I dont believe this will be an issue.  Im not even sure if Anoops solution is what I need, as I dont know how to incorporate it.  If you came up with a solution to trim Anoops code down I would be interested in how that would look with my code.
  Darnell replied to Anoop S
09-Feb-11 12:50 PM
I figured it out.  Thank you.
Create New Account
help
Macro Removes Cell Borders Excel Excel 2003 This is a pretty trivial issue, but it's one of those things that one tiny irritating flaw. When I run the MarkClear macro it also removes the cell borders. Can anyone tell me why that happens? - -Tom Excel Programming Discussions Excel 2003 (1) Interior (1) Borders (1) ActiveCell.Clear (1) Macros (1) Selection.Interior (1) Interior.ColorIndex (1) BuildRangeVariables (1) try End Sub Sub MarkClear() MarkRow Range(ColorCodeCell).Clear Range(ActionCell).Clear End Sub - - Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com Clear clears all formatting. Try using ClearContents
how can i print with the lines, in microsoft excel Excel I try to print a list of names, but the lines will not show up. Excel New Users Discussions Border (1) Excel (1) You need to either - insert borders - or turn on gridlines Regards. Bill Ridgeway Computer Solutions Bunmi. have you tried file, page the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 keywords: how, can, i, print, with, the, lines, , in, microsoft, excel description: I try to print a list of names, but the lines will not
How do I set up alternate row shading with Excel 2003? Excel Excel Miscellaneous Discussions Microsoft Excel (1) Excel 2003 (1) SUBTOTAL (1) COUNTA (1) MOD (1) ROW (1) Sheet.Regards (1) Qm2c44d9s8ipeual63muf3aah5raa77ouv (1 data. = MOD(ROW(), 2) = 0 will not retain the banding through filtering. Gord Dibben MS Excel MVP Hi Gordon, I didn't know about using subtotal to maintain banding. (Does that 2), 2) = 0 Format> Pattern to a nice light gray color. Nothing to do with borders. Note the placement of the $ signs. . . . . . . . .you want $A1 to be column absolute and row
Lock only borders Excel I want to create an exceel worksheet where the borders are protected from copy / paste and drag / drop but the users can still type in data. Thanks Excel Discussions Microsoft Excel (1) Excel (1) Worksheet (1) Checkmark (1) Cmcr34 (1) Exceel (1) By default all cells on a sheet are locked when sheet is protected. For Excel 2002 and newer versions. . . . . . . . . . . . . . . . . . . . . . . Select the cells to type in. . . . . . .presumably these cells have borders
Unable to click on charts & unable to create any new charts Excel Excel Hi Just started using Microsoft Excel 2007 and I seem to have done something that means the chart option is unavailable on them anywhere. I'm working on a project and need it finished by Monday. Excel Charting Discussions Microsoft Excel (1) Excel (1) Worksheet (1) Workbook (1) Snapclick (1) DCC6682EC622 (1) Bliengme (1) Caps (1) Do you