• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 88
  • Last Modified:

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.

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

Open in new window

CopyTest.xlsx
0
NVIT
Asked:
NVIT
  • 2
1 Solution
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try something like this......
Sub x()
Dim LastRow As Long, NextCol As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
If LastRow < 2 Then Exit Sub
Cells(1, NextCol) = "Comment"
Cells(2, NextCol).Borders(xlEdgeBottom).LineStyle = xlContinuous
Cells(2, NextCol).Copy
Range(Cells(2, NextCol), Cells(LastRow, NextCol)).PasteSpecial xlPasteAll
Cells(1, NextCol).Select
Application.CutCopyMode = 0
End Sub

Open in new window

0
 
Roy CoxGroup Finance ManagerCommented:
If you use a Table then Formatting is automatic, no need for a macro.

Introduction to Excel Tables
0
 
NVITAuthor Commented:
Thanks, Subohd! It works!
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now