CODEWITHSUNDEEP
vbaexcel
nabilah
nabilah

Reputation: 211

Removing the border lines in a worksheet when code has been run

I have a code that successfully looks into an external file and copy/pastes the rows that contain that particular condition into the current workbook. For example I am searching for Singapore in the external workbook called Active master project file and copy all the rows containing Singapore to the current workbook that is open.

A problem that occurs is that when I run the same code twice, a border line will exist on the last row of the worksheet. For example when I run the code, it will copy paste the information containing Singapore to the current worksheet called "New Upcoming Projects":

enter image description here

However, when I run the code again it will create a border line on each column such as the image shown below:

enter image description here

And the code that I have for now is:

Sub UpdateNewUpcomingProj()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim copyFrom As Range
    Dim lRow As Long '<~~ Not Integer. Might give you error in higher versions of excel
    Dim strSearch As String

    Set wb1 = Application.Workbooks.Open("U:\Active Master Project.xlsm")
    Set ws1 = wb1.Worksheets("New Upcoming Projects")

    strSearch = "Singapore"
    With ws1

        '~~> Remove any filters
        .AutoFilterMode = False

        '~~> I am assuming that the names are in Col A
        '~~> if not then change A below to whatever column letter
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        With .Range("A1:A" & lRow)
            .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
            Set copyFrom = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        End With

        .AutoFilterMode = False
    End With

    '~~> Destination File
    Set wb2 = ThisWorkbook
    Set ws2 = wb2.Worksheets("New Upcoming Projects")
     With ws2
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lRow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            lRow = 2
        End If

       copyFrom.Copy .Rows(lRow)
      .Rows.RemoveDuplicates Array(2), xlNo

    End With
End Sub

Is there any improvement or additional codes that I have to add in so that the border line would disappear?

Upvotes: 1

Views: 2532

Answers (5)

Isu
Isu

Reputation: 127

At the end of the code, please add a new line to format paint of the 3rd row.

So basically before the last two lines wb1.Select ' please make sure you select the correct one wb1 or wb2 here and try again Rows("3:3").Select Selection.Copy Rows("4:10000").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False end with end sub 'This is the last line of your code

Upvotes: 0

A.S.H
A.S.H

Reputation: 29332

You can add this line after removing the duplicates 

.UsedRange.Offset(lRow).Borders.Value = 0

This will remove any borders from the inserted rows

p.s.: I still dont understand where these borders came from, most probably from the original worksheet.. :)

Upvotes: 2

Davesexcel
Davesexcel

Reputation: 6984

Paste Special, this will paste to the first empty cell in column A

copyfrom.Copy
ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
Application.CutCopyMode = 0

Upvotes: 2

aucuparia
aucuparia

Reputation: 2051

I assume this formatting is coming from the source worksheet. If so, you could PasteSpecial to just paste values, keeping the destination formatting. To do so, simply replace

copyFrom.Copy .Rows(lRow)

with

copyFrom.Copy
.Rows(lRow).PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False

If you do need some formatting from the source sheet, you can use xlPasteAllExceptBorders instead of xlPasteValues.

Upvotes: 2

Kᴀτᴢ
Kᴀτᴢ

Reputation: 2176

As EyePeaSea said you can remove the border by vba code, e.g.

ThisWorkbook.Worksheets("XY").Range("A1", "Z99").Borders.LineStyle = xlNone

In your case the code should be (untested)

copyFrom.Borders.LineStyle = xlNone

after you copied the row

Upvotes: 4

Related Questions