Loop Macro to Auto Format Borders

[algirard8]

I am new to VBA and macros and have been looking around and trying to learn but there are several ways to do looping and I haven't grasped it yet. I am trying to create a loop that goes from cells A1-F1 until A#-F# where the next row is empty and apply borders to them all. What is the easiest way to do this? Thanks

[johnjohns]

For many of the simple tasks, it is easy to record a macro when we manualy do it first. Then you can re-run the macro with the necessary changes. This is what I did for your requirement. See the code

Sub Macro2()
    Range("A1:F1").Select
    Range(Selection, Selection.End(xlDown)).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
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
'       .ColorIndex = xlAutomatic
'       .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
'       .ColorIndex = xlAutomatic
'       .TintAndShade = 0
        .Weight = xlThin
    End With
    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
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
'       .ColorIndex = xlAutomatic
'       .TintAndShade = 0
        .Weight = xlThin
    End With
End Sub

rgds

johnjohns

[algirard8]

Thanks for the quick response. I did learn some from this post but I can't figure out how to modify the code to work for my application. I forgot to mention that cells A1-D1 through A#-D# are blank so I want to loop until column E is blank. Columns E and F will always contain data and end on the same row. Thanks

[johnjohns]

For this requirement this is what I did.
1 Started recording the macro
2 Selected the cell F1
3 Keeping the Shift Key pressed, striked End and Down Arrow Key
so that I selected till the last non-empty cell (the one before the first empty cell) in F
column
4 Keeping the Shift Key pressed, striked the Home key so that I got
the entire block including A-D columns
5 Formatted the selected cells with borders
6 Stopped recording the macro
7 Here is the result

Sub Macro2()
    Range("F1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Cells(ActiveCell.Row, 1)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
End Sub

rgds

johnjohns

[algirard8]

Thanks for the explanation. That makes sense that you are selecting the most right column first and then selecting the left columns to then add borders. I have another question. I was wondering how I could add column G to get borders. Column G does not contain any data at all. So basically there are blank columns on the left and right of the columns that contain data.

[algirard8]

I figured it out. I ended up just using column F but then selecting to the right so basically I do the format twice. Is there an easier way to do this?

[StephenR]

Perhaps you could just copy formatting across from F for those cells with data?

Range("F1", Range("F" & Rows.Count).End(xlUp)).Copy
Range("G1").PasteSpecial xlPasteFormats

[johnjohns]

Also try

    ActiveSheet.Range(Range("A1"), Range("F1").End(xlDown).Offset(0, 1)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

rgds

johnjohns

[algirard8]

Thanks for that more concise code. The code works perfectly. How would I go about adding a table a row below the last nonempty row. The problem I am having right now is that the actions that I am recording are set to a specific row number and the starting row of the table will change over time. I just don't know the proper key presses to cause the selection to go to the end of the data and then two more rows.

[StephenR]

If you've got data in column A this will insert borders around a block of cells 5x3 two rows below the data.

Range("A" & Rows.Count).End(xlUp).Offset(2).Resize(5, 3).Borders.LineStyle = xlContinuous