Format only columns with values

[BDexcel]

Hi all,

Could someone help me to amend the below code?

I use the below code on as part of copying varies results to a new tab and applying a border. Some of these extracts have 5 columns some have 10 so i used A2:Z... but obviously this borders all cells from A:Z.

I want the code to only border columns as far as the last one with values, could someone help?

lr = Cells(Columns.Count, "A").End(xlUp).Row
Range("A2:Z" & lr).Select
Selection.EntireColumn.AutoFit
With Selection.Borders
.LineStyle = xlContinuous
.Color = vbBlack
.Weight = xlThin
End With

 

[mumps]

Re: Help to format only columns with values

Try:

Sub addBorders()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    With Range(Cells(2, 1), Cells(LastRow, lCol))
        .Columns.AutoFit
        .Borders.LineStyle = xlContinuous
    End With
    Application.ScreenUpdating = True
End Sub

 

[BDexcel]

Re: Help to format only columns with values

unfortunately still doing the same mumps but appreciate the reply

 

[mumps]

Re: Help to format only columns with values

I tried the macro on a dummy sheet and it worked properly. Do you want the borders on the new sheet after you paste the data? If so, what is the name of that new sheet?

 

[BDexcel]

Re: Help to format only columns with values

yeah, sorry mumps... My code filters a main sheet and pastes into a new 'sheet1'

The only part i'm having bother with is bordering the new data as the extracted data can vary in size

 

[mumps]

Re: Help to format only columns with values

Try:

Sub addBorders()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lCol As Long
    lCol = Sheets("Sheet1").UsedRange.Columns.Count
    With Sheets("Sheet1").Range(Sheets("Sheet1").Cells(2, 1), Sheets("Sheet1").Cells(LastRow, lCol))
        .Columns.AutoFit
        .Borders.LineStyle = xlContinuous
    End With
    Application.ScreenUpdating = True
End Sub

 

[BDexcel]

Re: Help to format only columns with values

Thanks mumps! This worked perfect

 

[mumps]

Re: Help to format only columns with values

You are very welcome.