Hi, I am using excel 2010 and am trying to automate a tedious process that takes me about half an hour. The code below already works but it takes about 5-7mins to run.
Basically, I have a list of predefined numbers below that I insert into column A. Then I start at the far right of the table's header's and compare the unique predefined number of that header to the list I have put into column A. If the header's unique predefined number matches one of the numbers in my predefined list in column A the code moves on, if the header's unique predefined value does not match one of the numbers in my predefined list in column a, then it deletes the table's column. The table is very very very large and I'd like to speed up the code. I don't know if using arrays would do this or necessarily how to incorporate them. Any Ideas?
This code generally takes around 5 minutes to run all the way through, like I said, its a large table. I was thinking of making the predefined list in column A above into an array, and comparing the array to the variable ID_Number.
My question is, if I incorporated arrays in the above described way, would it make the code run faster, and if yes, then what would the code look like?
Thank you in advance for your input.
Basically, I have a list of predefined numbers below that I insert into column A. Then I start at the far right of the table's header's and compare the unique predefined number of that header to the list I have put into column A. If the header's unique predefined number matches one of the numbers in my predefined list in column A the code moves on, if the header's unique predefined value does not match one of the numbers in my predefined list in column a, then it deletes the table's column. The table is very very very large and I'd like to speed up the code. I don't know if using arrays would do this or necessarily how to incorporate them. Any Ideas?
Rich (BB code):
Sub example1()
statusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents
screenUpdateState = Application.ScreenUpdating
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim lastrow2 As Integer, lastcolumn1 As Integer, Counter As Integer, variable1 as string
Dim i As Integer, x As Integer, PctDone As Single
Dim ws As Worksheet, lstList As ListObject
Set ws = ActiveSheet 'Beg this basically makes a table into a range to speed up the code if there is a table
For Each lstList In ws.ListObjects
If ws.ListObjects(1) <> "" Then
Ticker = 1
ActiveSheet.ListObjects(1).Unlist
Exit For
End If
Next
If ActiveSheet.AutoFilterMode = True Then 'this turns off any filters
ActiveSheet.AutoFilterMode = False
End If
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Value = "ID Number" 'Inserts the predefined list into column A
Range("A2").Value = "10761"
Range("A3").Value = "10188"
Range("A4").Value = "10748"
Range("A5").Value = "10657"
Range("A6").Value = "10012"
Range("A7").Value = "10653"
Range("A8").Value = "10665"
Range("A9").Value = "10084"
Range("A10").Value = "11402"
Range("A11").Value = "10178"
Range("A12").Value = "10085"
Range("A13").Value = "10179"
Range("A14").Value = "11397"
Range("A15").Value = "11398"
Range("A16").Value = "10086"
Range("A17").Value = "10028"
Range("A18").Value = "11085"
Range("A19").Value = "11424"
Range("A20").Value = "10684"
Range("A21").Value = "10102"
Range("A22").Value = "11240"
Range("A23").Value = "11313"
Range("A24").Value = "10663"
Range("A25").Value = "10096"
Range("A26").Value = "10664"
Range("A27").Value = "10210"
Range("A28").Value = "11456"
Range("A29").Value = "10399"
Range("A30").Value = "10326"
Range("A31").Value = "10104"
Range("A32").Value = "10106"
Range("A33").Value = "10149"
Range("A34").Value = "10578"
Range("A35").Value = "11086"
Range("A36").Value = "10661"
Range("A37").Value = "10108"
Range("A38").Value = "10098"
Range("A39").Value = "10206"
Range("A40").Value = "10724"
Range("A41").Value = "11087"
Range("A42").Value = "10046"
Range("A43").Value = "10208"
Range("A44").Value = "10110"
Range("A45").Value = "10099"
Range("A46").Value = "10207"
Range("A47").Value = "10290"
Range("A48").Value = "10686"
Range("A49").Value = "10728"
Range("A50").Value = "10209"
Range("A51").Value = "10111"
Range("A52").Value = "10100"
Range("A53").Value = "10240"
Range("A54").Value = "10094"
Range("A55").Value = "10173"
lastrow1 = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row 'counts the number of predefined numbers in column a
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
lastcolumn2 = Selection.Columns.Count 'Counts the number of headers I need to cycle through
For i = lastcolumn2 To 21 Step -1 'Starts at the far right of the table's headers and goes toward the left (step-1)
If Cells(1, i).Value <> "" Then 'if the header value does not equal ""
ID_Number = Left(Right(Cells(1, i).Value, 6), 5) 'trims out the predefined header value
Cells(1, 1).Value = ID_Number 'puts the trimmed out header value in range A1
For x = 1 To lastrow1 'For x from 1 to last row of the predefined list entered in column A
If Cells(1, 1).Value = Cells(1 + x, 1).Value Then 'if trimmed header value in range A1 is equal to any of the values in the predefined list in column A
Exit For 'if its true, exit the for statement move to the next header
End If
Counter = Counter + 1 'adds a counter for the progress bar form
Next
If x > lastrow1 Then 'if x is greater than the variable lastrow1, which if none of the values in the above for statement matched the value in cell A1, then x will be greater than the variable lastrow1
Range(Cells(1, i), Cells(lastcolumn2, i)).Delete 'then delete the table column
End If
PctDone = Counter / (lastcolumn2 * lastrow1) 'progress bar stuff
With UserForm2 'progress bar stuff
.FrameProgress.Caption = Format(PctDone, "0%") 'progress bar stuff
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10) 'progress bar stuff
End With 'progress bar stuff
' The DoEvents statement is responsible for the form updating 'progress bar stuff
DoEvents 'progress bar stuff
End If
Next
Unload UserForm2 'progress bar stuff
Unload UserForm1 'progress bar stuff
Columns("A:A").Delete 'deletes the predefined list I inserted into column A
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
lastcolumn2 = Selection.Columns.Count
lastrow2 = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(2, 1), Cells(lastrow2, lastcolumn2)), , xlYes).Name = "Table1" 'reinserts a table if I deleted it at the beginning, and even if I didn't delete it at the begininng
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
Application.ScreenUpdating = screenUpdateState
End SubMy question is, if I incorporated arrays in the above described way, would it make the code run faster, and if yes, then what would the code look like?
Thank you in advance for your input.
Last edited by a moderator:
