VB script to select all rows and columns with data so as to set borders

[fornight]

Hi,

I have this worksheet with downloaded data. It has many columns and rows of data. I need to create borders for them - just the side borders (left and right) of each cells. I need a script that will select all the cells in those columns for that. I tried building it by picking up bits and pieces from macro recording. I realise I cannot use select script with:

1. Sheets("xx").Cells.Select = because it will create borders for unused columns in the worksheet.

2. Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

= because not all the cells in the first column will be populated with data. it might not pick up all the rows.

3. Range (A:J).Select = this is too specific and I have no control over when there might be more columns of data available in future for the download.

This is my script so far:

ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("All - Basic Data").Select
Sheets("All - Basic Data").Cells.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFilter
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
Range("I:I,R:R,S:S").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-"
Range("A:G,J:Q").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveWindow.Zoom = 80
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Sheets("All - Basic Data").Cells.EntireColumn.AutoFit

I built this VB script based on picking up bits and pieces of macro record, thus it is not very sleek.

[Kramxel]

This code will give you the column number of the last column that contains data, you can then use this to format the range if need be.

Sub Test()
    Dim ws As Worksheet
    Dim rng As Range
    Dim LastCol As Long

    Set ws = Sheets("All - Basic Data")

    With ws
        LastCol = .Cells.Find(What:="*", After:=.Range("A1"), Lookat:=xlPart, _
        LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
        MatchCase:=False).Column
    End With
    
End Sub

[fornight]

Hi,
What do I do with the column number?

I can use "Range(Selection, Selection.End(xlToRight)).Select" to select all the used columns, can't I?

My issue is to try to pick up all the rows as well because not all the cells in every rows will be populated.

[Kramxel]

Sorry - this will look through each column and each row to set the other limits of the region with data, it will then loop through each cell and check to see if its empty. if it isn't It adds left and right borders, otherwise it moves on.

Sub Test()
    Dim ws As Worksheet
    Dim rng As Range, cell As Range
    Dim LastCol As Long, LastRow As Long

    Set ws = Sheets("Magic")
    
    With ws
        
        LastCol = .Cells.Find(What:="*", After:=.Range("A1"), Lookat:=xlPart, _
        LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
        MatchCase:=False).Column
        
        LastRow = .Cells.Find(What:="*", After:=.Range("A1"), Lookat:=xlPart, _
        LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        MatchCase:=False).Row
        
    For Each cell In .Range(.Cells(1, 1), .Cells(LastRow, LastCol))

        
        If Not cell.Value = "" Then
           With cell.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlMedium
           End With
           
           With cell.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlMedium
           End With
        Else
        End If
        
    Next cell
    
    End With
    
    
End Sub

[fornight]

I am sorry. I have almost no visual basic knowledge other then copying the editing what is written by the macro recorder.

I tried to remove what I originally have for borders in my script and insert in yours portion and the rest of my formatting does not work anymore.

In addition, my script is actually longer where it will go to the next worksheet and do similar....that also does not work anymore. I think it was due to the setting of Dim set in your script.

[Kramxel]

This now adds the borders styles you've specified in your code, and checked every worksheet in the workbook.

Sub Test()
    Dim ws As Worksheet
    Dim rng As Range, cell As Range
    Dim LastCol As Long, LastRow As Long
    
        For Each ws In ActiveWorkbook.Worksheets 'For every worksheet in our Workbook
        
            With ws 'we're going to use the worksheet
            
                LastCol = .Cells.Find(What:="*", After:=.Range("A1"), Lookat:=xlPart, _
                LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
                MatchCase:=False).Column 'and check every column until we find the last one that contains data
                
                LastRow = .Cells.Find(What:="*", After:=.Range("A1"), Lookat:=xlPart, _
                LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
                MatchCase:=False).Row   ''and check every row until we find the last one that contains data
                
                    For Each cell In .Range(.Cells(1, 1), .Cells(LastRow, LastCol)) 'then starting with Cell A1 upto our last row and column
                    
                        If Not cell.Value = "" Then ' if the cell isn't empty
                        
                            With cell.Borders(xlEdgeRight) 'apply a right hand border with the attributes below
                                .LineStyle = xlContinuous
                                .Weight = xlThin
                            End With
                            
                            With cell.Borders(xlEdgeLeft) 'and a left hand border with the attributes below
                                .LineStyle = xlContinuous
                                .Weight = xlThin
                            End With
                        
                        Else
                        End If
                    
                    Next cell 'move onto the next cell in the worksheet
            
            End With 'stop working with the worksheet
        
        Next ws ' move onto the next worksheet and start again at the begining.

End Sub

[

[fornight]

it works!!! Thanks!

[Kramxel]

you're welcome.