Hi Guys,
From another thread (http://www.excelforum.com/excel-prog...30#post3213330)
My VBA is self taught and doubtless lacks good structure. One thing that I'm particularly curious about is the methods used between a macro that I wrote to solve the problem on the above thread and another written by another member whose code does essentially the same exercise but in a fraction of the time that mine runs.
My code was.
Sub s4driver()
Dim rng As Range
Dim LR As Long
Dim c As Range
LR = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Set rng = Sheet3.Range("b2:b" & LR)
For Each c In rng
If IsNumeric(Sheet3.Range(c.Address).Value) = False Or Left(Sheet3.Range(c.Address).Value, 2) = "00" Or Sheet3.Range(c.Address).Value = "" Then
Sheet3.Range("a" & c.Row & ":h" & c.Row).ClearContents
End If
Next c
End Sub
The better solution and enormously faster solution was posted by stanleydgromjr as follows.
Option Explicit
Sub ReorgAthruH()
' stanleydgromjr, 04/25/2013
' http://www.excelforum.com/excel-programming-vba-macros/918047-need-macro-to-remove-unwanted-data-from-large-tables.html
Dim a As Variant, b As Variant, lr As Long, i As Long, ii As Long, iii As Long
lr = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
a = Range("A2:H" & lr)
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
For i = 1 To UBound(a, 1)
If Left(a(i, 2), 2) = "00" Or a(i, 2) = "" Or a(i, 2) Like "*[A-Z,a-z]*" Then
'do nothing
Else
iii = iii + 1
For ii = 1 To UBound(a, 2)
b(iii, ii) = a(i, ii)
Next ii
End If
Next i
Range("A2:H" & lr) = b
End Sub
I'm keen to get some help and advice on better coding practice and structure and also to understand why there is such a large difference in the execution time between the macros.
Many thanks for any help and advice.
Chris
Bookmarks