i have this vba code that ive been working on since last year and i cant seem to run it without excel crashing when i add more data to it.
is there any way to tidy this up or make it run more efficiently (faster)?
i have 100 rows x 6 columns but sometimes up to 15 columns wide.
I just cant seem to speed it up. please help. thanks in advance.
is there any way to tidy this up or make it run more efficiently (faster)?
i have 100 rows x 6 columns but sometimes up to 15 columns wide.
I just cant seem to speed it up. please help. thanks in advance.
Code:
Sub Permute()
Application.ScreenUpdating = False
Dim ix(100, 1) As Long, rc As Long, m As Long, br As Long, md As Variant, i As Long, r As Long
Dim str1 As String, r1 As Long, c1 As Long, element(100) As Variant
rc = Cells(1, Columns.Count).End(xlToLeft).Column
m = 0
For i = 1 To rc
br = Cells(Rows.Count, i).End(xlUp).Row
If br > m Then m = br
ix(i, 0) = br
ix(i, 1) = 1
Next i
md = Range(Cells(1, 1), Cells(m, rc)).Value
r = 0
Incr:
str1 = ""
For i = 1 To rc
str1 = str1 & md(ix(i, 1), i)
element(i) = md(ix(i, 1), i)
Next i
MyCode:
If Mid(element(1), 1, 1) = Mid(element(2), 1, 1) And _
Mid(element(1), 1, 1) = Mid(element(3), 1, 1) And _
Mid(element(2), 1, 1) = Mid(element(3), 1, 1) And _
Mid(element(1), 2, 1) = Mid(element(4), 1, 1) And _
Mid(element(1), 2, 1) = Mid(element(5), 1, 1) And _
Mid(element(4), 1, 1) = Mid(element(5), 1, 1) And _
Mid(element(2), 2, 1) = Mid(element(4), 2, 1) And _
Mid(element(2), 2, 1) = Mid(element(6), 1, 1) And _
Mid(element(4), 2, 1) = Mid(element(6), 1, 1) And _
Mid(element(3), 2, 1) = Mid(element(5), 2, 1) And _
Mid(element(3), 2, 1) = Mid(element(6), 2, 1) And _
Mid(element(5), 2, 1) = Mid(element(6), 2, 1) Then _
r = r + 1
r1 = ((r - 1) Mod Rows.Count) + 1
c1 = Int((r - 1) / Rows.Count) + 1
Sheets("Sheet2").Cells(r1, c1) = str1
End If
For i = rc To 1 Step -1
ix(i, 1) = ix(i, 1) + 1
If ix(i, 1) <= ix(i, 0) Then Exit For
ix(i, 1) = 1
Next i
If i > 0 Then GoTo Incr:
End Sub
