More efficient way to write this without using "Select"?

[288enzo]

I'm using select twice to achieve my result of adding horizontal boarders. Is there perhaps a better way to write this?

The far right column can change, as can the bottom row.

    Range(Range("A4"), Range("A4").End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
    Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
    Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous

Thank you

 

[Michael M]

Maybe this way

Sub MM1()
Dim lr As Long, lc As Integer
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
lc = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
Range(Cells(4, 1), Cells(lr, lc)).Borders(xlInsideHorizontal).LineStyle = xlContinuous
End Sub

 

[BSALV]

doesn't work with row 1 !

    With Range("A4")                                           'your topleftcell
          k = .End(xlToRight).Column                            'last column number
          r = .End(xlDown).Row                                  'last row number
          .Offset(-1).Resize(r - .Row + 3, k).Borders(xlInsideHorizontal).LineStyle = xlContinuous     'go 1 row higher and number of row +3 then everything is inside
     End With

 

[bebo021999]

Range("A4").UsedRange.Select
  With Selection
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlInsideHorizontal).LineStyle = xlContinuous
End With

 

[288enzo]

Maybe this way

Sub MM1()
Dim lr As Long, lc As Integer
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
lc = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
Range(Cells(4, 1), Cells(lr, lc)).Borders(xlInsideHorizontal).LineStyle = xlContinuous
End Sub

Fantastic, thank you.

I get lr as Long, why lc as Integer and not Long? Does it have something to do with it representing columns and not rows?

 

[288enzo]

Range("A4").UsedRange.Select
  With Selection
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlInsideHorizontal).LineStyle = xlContinuous
End With

This looked interesting, but on the first line I get Object doesn't support this property or method. Are you using 365? I'm on 2016

Thank you

 

[Michael M]

Does it have something to do with it representing columns and not rows?

Correct !!

 

[BSALV]

there are +16,000 columns and +1,000,000 in a worksheet
integer is until +32,000, so columns are okay with integers, rows aren't !!!

replace .usedrange by .currentregion

 

[288enzo]

there are +16,000 columns and +1,000,000 in a worksheet
integer is until +32,000, so columns are okay with integers, rows aren't !!!

replace .usedrange by .currentregion

Thank you for the additional info!

Much appreicated

 

[bebo021999]

This looked interesting, but on the first line I get Object doesn't support this property or method. Are you using 365? I'm on 2016

Thank you

Not test with PC. But try again:

  With Range("A4").CurrentRegion
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlInsideHorizontal).LineStyle = xlContinuous
End With