Copy and Remove Dups

chipsworld

Board Regular
Joined
May 23, 2019
Messages
120
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!

VBA Code:
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

JB2020

Board Regular
Joined
Jul 29, 2020
Messages
75
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
Are there formulas being copied across? It could be links to another workbook which are increasing the size.
 

chipsworld

Board Regular
Joined
May 23, 2019
Messages
120
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

Board Regular
Joined
Jul 29, 2020
Messages
75
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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

Board Regular
Joined
May 23, 2019
Messages
120

ADVERTISEMENT

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

VBA Code:
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

Board Regular
Joined
May 23, 2019
Messages
120
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

Board Regular
Joined
Jul 29, 2020
Messages
75
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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

Board Regular
Joined
May 23, 2019
Messages
120
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,128,000
Messages
5,628,056
Members
416,289
Latest member
Jbelisari

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top