Macro creates empty rows

[DSLA]

Hello everyone.

In one file we have a macro that collects data from a datafile(other excel) according to the customer number(s).

Here is the code: (the code runs through several fields and gets the sales data according to the customer number in the cell (Range. "D16") and collects it to the CUSTOMER_DATA-sheet)

Dim varFileName1 As String
varFileName1 = Application.ActiveWorkbook.Name
Workbooks.Open Filename:="C:\DATAFILE.XLS"
Dim WB1 As Workbook
Set WB1 = ActiveWorkbook
With WB1.Sheets("DATASHEET")
With .Range("A1:Z65500")
Windows(varFileName1).Activate
.AutoFilter Field:=1, Criteria1:=Sheets("INFOCARD").Range("D16")
.Copy Sheets("CUSTOMER_DATA").Range("A1")
.AutoFilter
End With
End With
WB1.Close savechanges:=False
End If


The problem is that now the code collect the data, but for some reason created 32 000 empty cells also (that is the amount of rows that the DATAFILE.XLS has rows). And the filesize goes up from under 200kb to 2500kb.

Before the code just got the values it found on the search, but now it creates also the empty cell.

Can anyone help me with this one? Please ask if something wasn't clear.

Thanx in advance.

 

[wigi]

- you should copy only the visible cells (specialcells(xlcelltypevisible))
- instead of a hardcoded range like .Range("A1:Z65500"), why not .Range("A1").CurrentRegion ?

 

[DSLA]

The code you suggested works out great in the future, but now i have about 1500 workbooks that have the macro in them, so changing that would be difficult.

Any more suggestions?

 

[wigi]

I do not understand what you mean with:

Before the code just got the values it found on the search, but now it creates also the empty cell.

So, what changed?

Is it correct that you have the same code in 1500 workbooks... ?

 

[DSLA]

Last month when i updated the workbooks (all the 1500 of them), the macro didn't create empty cells, but now when i updated the workbooks, it created the empty cells.

Only thing that changed, is the DATAFILE.XLS, it had 28 000 rows, and now it has 38 000 rows.

To clear this matter: the file that searches the values from datafile.xls, collects them to rows between 1-2000, but now it also created empty rows (no values in them) after that to row number 38 000, for some reason it doesn't figure out that the extra rows are not needed.

I do not understand what you mean with:



So, what changed?

Is it correct that you have the same code in 1500 workbooks... ?

 

[DSLA]

Any helps?

 

[DSLA]

Any helps?

 

[wigi]

Try uploading your file (without critical information obviously) to a filesharing website. Without seeing the files, I am not sure any help can still be given.

 

[DSLA]

Try uploading your file (without critical information obviously) to a filesharing website. Without seeing the files, I am not sure any help can still be given.

Here are the files:

Customercard, which has the Update button on the 2nd page.
http://www.setit.fi/xls/Customercard.xls

Datafile, from which the data in picked by the macro in the customercard.
http://www.setit.fi/xls/datafile.xls


Note the two files have to be in c:\ -location (it is defined in the macro)

 

[DSLA]

Any helps?