Macro increases the size of the Excel

[lunatu]

Hi,

Im having this code below to copy+paste data from sheet1 to sheet2, but running it seems to make my file HUGE. Any ideas how to solve this?
I have 2000 rows data in sheet1 so no more rows can be copied into sheet2. Now I think the code effects all rows in sheet2 (?).

Sub CopyCell()

Dim LR As Long, i As Long
With Sheets("Sheet1")
LR = .Range("H" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If .Range("H" & i).Value = "SalesPerson1" And .Range("J" & i).Value = "Sold" Then
dlr = Sheets("Sheet2").Cells(Rows.Count, "D").End(xlUp).Row + 1
.Range("A" & i).Resize(1, 9).Copy Destination:=Sheets("Sheets2").Range("D" & dlr)
Sheets("Sheets2").Range("C" & dlr) = Date
End If
Next i
End With

End Sub

 

[offthelip]

try this code which uses varaint arrays which wil lbe much faster and hoepfully will not increase the size of your file:

Sub CopyCell()

Dim LR As Long, i As Long
Dim outarr() As Variant
With Sheets("Sheet1")
LR = .Range("H" & Rows.Count).End(xlUp).Row
inarr = .Range(.Cells(1, 1), .Cells(LR, 10))
ReDim outarr(1 To LR, 1 To 10)
dlr = Sheets("Sheet2").Cells(Rows.Count, "D").End(xlUp).Row + 1
indi = 1
For i = 1 To LR
'If .Range("H" & i).Value = "SalesPerson1" And .Range("J" & i).Value = "Sold" Then
If (inarr(i, 8) = "SalesPerson1" And inarr(i, 10) = "Sold") Then
 For j = 2 To 10
  outarr(indi, j) = inarr(i, j)  ' copy a line
  outarr(indi, 1) = Date
  indi = indi + 1
 Next j
End If
Next i

Sheets("Sheets2").Range(Cells(dlr, 4), Cells(dlr + indi - 1, 14)) = outarr
End With
End Sub

Note is the name of your destination sheet really "Sheets2" not "Sheet2" ???

 

[lunatu]

Thanks! Not familiar with that code and getting an error when running it:

And yes, I meant "Sheet2"

 

[offthelip]

since you mean "Sheet2" this line will cause the subscript out of range error because it can't find "Sheets2"

Sheets("Sheets2").Range(Cells(dlr, 4), Cells(dlr + indi - 1, 14)) = outarr

so change it to:

Sheets("Sheet2").Range(Cells(dlr, 4), Cells(dlr + indi - 1, 14)) = outarr

 

[lunatu]

Yes I changed that when I noticed there is a typo... it gives an error here:

 

[offthelip]

sorry my mistake I put the indi = indi = 1 in the wrong place:

Sub CopyCell()

Dim LR As Long, i As Long
Dim outarr() As Variant
With Sheets("Sheet1")
LR = .Range("H" & Rows.Count).End(xlUp).Row
inarr = .Range(.Cells(1, 1), .Cells(LR, 10))
ReDim outarr(1 To LR, 1 To 10)
dlr = Sheets("Sheet2").Cells(Rows.Count, "D").End(xlUp).Row + 1
indi = 1
For i = 1 To LR
'If .Range("H" & i).Value = "SalesPerson1" And .Range("J" & i).Value = "Sold" Then
If (inarr(i, 8) = "SalesPerson1" And inarr(i, 10) = "Sold") Then
 For j = 2 To 10
  outarr(indi, j) = inarr(i, j)  ' copy a line
  outarr(indi, 1) = Date
 Next j
  indi = indi + 1
End If
Next i

Sheets("Sheets2").Range(Cells(dlr, 4), Cells(dlr + indi - 1, 14)) = outarr
End With
End Sub

 

[Alex Blakenburg]

@offthelip, you might want to add these too.

Dim inarr As Variant
Dim dlr As Long, indi As Long, j As Long

 

[offthelip]

Personally I don't like to clutter the top of my programs with loads of unnecessary declarations. This is partly because the typing in VBA is very poor, there very few types and excel doesn't enforce types consistently. ( I was an ADA programmer for while)

Declaring a variable at the top often doesn't provide me with any additional information at all. With EXCEL everything that is read from or written to the worksheet must be a variant. So the statement:

inarr = .Range(.Cells(1, 1), .Cells(LR, 10))

will automatically declare inarr as a variant array. In a similar way the first use of dlr means it contains a row number. which tells me far more that declaring it. The use of i , j and indi are clearly used as integer indices . The one declaration I did make was because it was essential to get the code to work correctly. this was the declaration of outarr. So I only tend to declare something when it needs to be declared, such as objects ,dictionaries. specific arrays, classes. I find this makes the list of declarations at the top of program much more useful because I just the list of things I need to know about. Everything else is just what I assume it to be. This is particularly helpful if the name of the variable means something: Just as an example of how little declaration helps with the readability of code, compare these two versions one with everything declared and the other with only the essential declaration:

Sub CopyCell()

Dim AA1 As Long, AA2 As Long
Dim AA3() As Variant
Dim AA4 As Variant
Dim AA5 As Long, AA6 As Long, AA7 As Long
With Sheets("Sheet1")
AA1 = .Range("H" & Rows.Count).End(xlUp).Row
AA4 = .Range(.Cells(1, 1), .Cells(AA1, 10))
ReDim outarr(1 To AA1, 1 To 10)
AA5 = Sheets("Sheet2").Cells(Rows.Count, "D").End(xlUp).Row + 1
AA6 = 1
For AA2 = 1 To AA1
'If .Range("H" & i).Value = "SalesPerson1" And .Range("J" & i).Value = "Sold" Then
If (AA4(AA2, 8) = "SalesPerson1" And AA4(AA2, 10) = "Sold") Then
 For AA7 = 2 To 10
  AA3(AA6, AA7) = AA4(AA2, AA7)  ' copy a line
  AA3(AA6, 1) = Date
 Next AA7
  AA6 = AA6 + 1
End If
Next AA2
Sheets("Sheets2").Range(Cells(AA5, 4), Cells(AA5 + AA6 - 1, 14)) = AA3
End With
End Sub

Sub CopyCell()


Dim outarr() As Variant
With Sheets("Sheet1")
LR = .Range("H" & Rows.Count).End(xlUp).Row
inarr = .Range(.Cells(1, 1), .Cells(LR, 10))
ReDim outarr(1 To LR, 1 To 10)
dlr = Sheets("Sheet2").Cells(Rows.Count, "D").End(xlUp).Row + 1
indi = 1
For i = 1 To LR
'If .Range("H" & i).Value = "SalesPerson1" And .Range("J" & i).Value = "Sold" Then
If (inarr(i, 8) = "SalesPerson1" And inarr(i, 10) = "Sold") Then
 For j = 2 To 10
  outarr(indi, j) = inarr(i, j)  ' copy a line
  outarr(indi, 1) = Date
 Next j
  indi = indi + 1
End If
Next i

Sheets("Sheets2").Range(Cells(dlr, 4), Cells(dlr + indi - 1, 14)) = outarr
End With
End Sub

I do accept this is slightly artificial since nobody in the right minds would code like this. but it just an example how little the declaration add to understanding the code.
I also understand that a lot of companies require every variable to be declared, but good coding standards require far more than making the declarations and unfortunately often that is all that is done in trying enforce good coding standards. ( as per the code above)

 

[Alex Blakenburg]

@offthelip, I agree that names that mean something are important for making the code readable, unfortunately with Option Explicit turned on the code won't run without declaring the variables.

 

[offthelip]

That is why I run without option explicit, it causes me far more problems that any possible help. I was brought on code where option explicit didn't exist. so I am in the habit of being very careful of how i psell (sic) things and using sensible names so that a misspelling is obvious by looking at it.