NVIT
asked on
Select range regardless of row count
I have a macro that was originally recorded. It creates a Comment column. Then adds bottom borders to the cells in that column. It works. But, when the row count change, e.g. gets less, I end up with 'extra' cells bottom borders.
It seems the issue starts at the Range("B2:B10").Select line.
Would you please adjust the code to add borders only on rows that have values in the ColA column? I've included the sample worksheet and code.
It seems the issue starts at the Range("B2:B10").Select line.
Would you please adjust the code to add borders only on rows that have values in the ColA column? I've included the sample worksheet and code.
Sub x()
'
' Add_Comment_Col Macro
'
'
Range("A1").Select
Selection.End(xlToRight).Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "Comment"
Range("B2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("B2").Select
Selection.Copy
Range("A3").Select
Selection.End(xlDown).Select
' Move one cell right
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Range("B2:B10").Select
Range("B10").Activate
ActiveSheet.Paste
Range("A1").Select
End Sub
CopyTest.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, Subohd! It works!
You're welcome. Glad to help.
Introduction to Excel Tables