Excel Macro Help?

Question

Using a loop, I'm trying to copy and paste the entire row of Cells with true statement to another worksheet


it's simple to just record and throw in an "if" statment and a loop. I'm running about 30,000 lines. I also want to copy all true statement all at once. And paste them ALL AT ONCE, if possible

Answer 1

If you are wanting to copy and paste the cells just that once, you might just try applying an autofilter. Set the filter for the one column to true then copy and paste only your true values.

If you have to use a macro, then I recommend using the UsedRange property along with transferring the values to an array. Then copy only the true values from that array to a new array and paste that data back into Excel.

Arrays handle the data a lot faster.

Here's an example of how you might do it:

Dim MyTempArray() As Variant
Dim MyTempArray2() As Variant
Dim TotalRows As Long
Dim TotalColumns As Integer
Dim x As Long
Dim y As Long
Dim z As Integer
Dim MyCounter As Long
Dim MyCounter2 As Long

TotalRows = ActiveSheet.UsedRange. _
Rows.Count
TotalColumns = ActiveSheet.UsedRange. _
Columns.Count

ReDim MyTempArray(1 To TotalRows, 1 To TotalColumns)

MyTempArray = ActiveSheet.UsedRange.Value

MyCounter = 0
For x = 1 To TotalRows
If MyTempArray(x, 3) = "True" Then
MyCounter = MyCounter + 1
Next x

ReDim MyTempArray2(1 To MyCounter, 1 To TotalColumns)

MyCounter2 = 0
For y = 1 To TotalRows
If MyTempArray(y, 3) = True Then
MyCounter2 = MyCounter2 + 1
For z = 1 To TotalColumns
MyTempArray2(MyCounter2, z) = MyTempArray(y, z)
Next z
End If
Next y

Worksheets.Add
Range(Cells(1, 1), Cells(MyCounter, TotalColumns)).Value = MyTempArray2

Answer 2

Why dont you simply filter on the rows that have a true statement. You could paste your IF formula in an adjacent column and then use auto filter to select just those rows where the result is "TRUE". You could then simply copy and paste those rows to another sheet.