CODEWITHSUNDEEP
vbaexcel
user7327273
user7327273

Reputation: 41

How to use an array function with an array in code

The below is an extract from some code I have produced to automate some of the processes in my job. One element of the macro I produced is to remove any grades out of scope of my report. Since the out of scope grades are always changing, but the in scope grades are set, I decided to try and use an array. I have never used these before and I found some code online to use as a template, the problem is that the code seems to flag all grades as 'false' whether they are in the array or not. I have checked the range and column by changing delete to setting interior colour and this confirms the column and range is correct. I think the issue is that I have not properly linked the function with the code in the sub. Any advice or suggestions will be appreciated:

Sub SortData()
Dim firstrow As Long
Dim LastRow As Integer
Dim arrGrades As Variant, grd As Variant

arrGrades = Array("Range B", "Range C", "Range D Experienced", "Range D", "Range E", "Range E2", "SCS 1", "SCS 2", "SCS 3", "Student")

With Sheets("Active OoD")
    .Select

    firstrow = .UsedRange.Cells(1).Row
    LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

    Set rng = Range("H2", "H" & LastRow)
    With rng
        For i = .Rows.Count To 1 Step -1
           If IsInArray(.Item(i), arrGrades) = False Then
                .EntireRow.Delete
            End If
        Next i
    End With
End With
End Sub

Function colNumeric(ColAlpha As String)
 ColAlpha = UCase(ColAlpha)
 If Len(ColAlpha) = 3 Then
     Col_no = (Asc(Left(ColAlpha, 1)) - 64) * 26 * 26 + _
              ((Asc(Mid(ColAlpha, 2, 1)) - 64) - 1) * 26 + _
              Asc(Right(ColAlpha, 1)) - 64
 ElseIf Len(ColAlpha) = 2 Then
     Col_no = (Asc(Left(ColAlpha, 1)) - 64) * 26 + _
              (Asc(Right(ColAlpha, 1)) - 64)
 Else
     Col_no = Asc(Right(ColAlpha, 1)) - 64
 End If
End Function

Function IsInArray(valToBeFound As Variant, arr As Variant) As Boolean
'Function IsInArray(grd As Variant, arrGrades As Variant) As Boolean
'INPUT: Pass the function a value to search for and an array of values of any data type.
'OUTPUT: True if is in array, false otherwise
Dim element As Variant
On Error GoTo IsInArrayError: 'array is empty
    For Each element In arr
        If element = valToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next element
Exit Function
IsInArrayError:
On Error GoTo 0
IsInArray = False
End Function

Upvotes: 0

Views: 70

Answers (1)

Domenic
Domenic

Reputation: 8124

This line...

.EntireRow.Delete

...refers to the entire row for the entire range. So, for example, if Rng refers to the range H2:H10, EntireRow refers to $2:$10, hence everything gets deleted. Instead, try referring to the current row as follows...

.Item(i).EntireRow.Delete

Upvotes: 3

Related Questions