Solved

Select range regardless of row count

Posted on 2016-09-02
4
52 Views
Last Modified: 2016-09-06
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
Comment
Question by:NVIT
  • 2
4 Comments
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41782537
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
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41782560
If you use a Table then Formatting is automatic, no need for a macro.

Introduction to Excel Tables
0
 
LVL 23

Author Closing Comment

by:NVIT
ID: 41786663
Thanks, Subohd! It works!
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41786664
You're welcome. Glad to help.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Outlook Free & Paid Tools
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now