Copy and Remove Dups

[chipsworld]

HI all,
Need a hand please...

I am puzzled as to why the below code takes my file from 3.5mb to 100mb after running it once.
I thought it was empty rows being added, but with the code to delete empty rows, it is still happening.

Where did I go wrong...

All I am doing is copying data to my active workbook from another version of the same workbook, then eliminate dups. Not sure how adding 3 to 10 rows of data could increase the file size so much...

Thanks in advance for any help!

Public Sub import_new_data()
' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
Dim targetsheet As Worksheet
Dim sourceSheet As Worksheet
Dim response As String
Dim LastRow As Long

On Error Resume Next

response = MsgBox("Please make sure you select the correct historical file to import" _
& vbCrLf & "Are you sure you want to Proceed?", vbYesNo, "ALERT!!!!")

    If response = vbYes Then

Set targetWorkbook = Application.ThisWorkbook
Set targetsheet = targetWorkbook.Sheets("Historical")

' get the customer workbook
filter = "*.xl* (*.xls*),*.xls*"
caption = "Please Select file to import "
customerFilename = Application.GetOpenFilename(filter, , caption)

Set customerWorkbook = Application.Workbooks.Open(customerFilename)

Set sourceSheet = customerWorkbook.Sheets("Historical")

' copy data from customer to target workbook


Dim LRSrc As Long, LRDest As Long, SrcRng As Range
With sourceSheet
LRSrc = .Cells(.Rows.Count, 2).End(xlUp).Row 'assumes column 2 is contiguous
Set SrcRng = .Range("A2:AJ" & LRSrc) 'starts at row 2 to avoid header
End With
With targetsheet
LRDest = .Cells(.Rows.Count, 2).End(xlUp).Row 'assumes column 2 is contiguous
SrcRng.Copy .Cells(LRDest + 1, 1)
End With


Call Module1.Remove_Dups

Else: response = vbNo

MsgBox "Nothing was Imported", vbOKOnly

End If
' Close customer workbook
customerWorkbook.Close (False)
End Sub

Public Sub Remove_Dups()
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Historical")

With ws
.UsedRange.RemoveDuplicates Columns:=2, Header:=xlYes
'.Columns("A:AJ").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With

End Sub

 

[JB2020]

Are there formulas being copied across? It could be links to another workbook which are increasing the size.

 

[chipsworld]

Nope...just straight data. The data being copied is created by a lot of VBA, but there are no formulas on the sheet, nor VBA in the sheet background.

 

[JB2020]

Nope...just straight data. The data being copied is created by a lot of VBA, but there are no formulas on the sheet, nor VBA in the sheet background.

Maybe formatting? You could try changing to only paste values and see if that has any impact?

 

[chipsworld]

OK...I ran it with just the remove dups portion and made a small change to the code to only run to the Last Row instead of USED RANGE and all things are good. No change in file size.

When it runs with the part that removes the "Blank Lines", the file size shoots from 3.5mb to 100mb.

I'm guessing that the blank line removal is just not that important! LOL

Public Sub Remove_Dups()
Dim ws As Worksheet
Dim LastRow As Long


Set ws = ThisWorkbook.Sheets("Historical")

With ws
LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
.Range("A2:AJ" & LastRow).RemoveDuplicates Columns:=2, Header:=xlYes
    End With

End Sub

 

[chipsworld]

UPDATE:
When running the code using .usedrange.RemoveDuplicates Columns:=2, Header:=xlYes , the file size increases to 99.6 mb from 3.4mb but when I use .Range("A2:AJ2" & LastRow).RemoveDuplicates Columns:=2, Header:=xlYes it doesn't remove the dups...

HELP! I can not figure this out.

The sheet currently has ~1500 rows with data, and the import is around the same number of rows with 95% being dups. I am trying to just keep the new stuff and obviously get rid of the duplicate lines.

What am I doing wrong?

 

[JB2020]

UPDATE:
When running the code using .usedrange.RemoveDuplicates Columns:=2, Header:=xlYes , the file size increases to 99.6 mb from 3.4mb but when I use .Range("A2:AJ2" & LastRow).RemoveDuplicates Columns:=2, Header:=xlYes it doesn't remove the dups...

HELP! I can not figure this out.

The sheet currently has ~1500 rows with data, and the import is around the same number of rows with 95% being dups. I am trying to just keep the new stuff and obviously get rid of the duplicate lines.

What am I doing wrong?

I would check what your LastRow variable is whilst it is running. You can just add a line in with msgbox LastRow after "LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row" for a quick way of checking, or use Debug.Print or the locals window.

 

[chipsworld]

Thanks JB2020...I just figured it out. Those damned periods will kill you!

As always to the forum (and JB2020)...thank you! Always a good experience!