0

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
3
  • This doesn't answer your question, but I think your colNumeric function can be replaced by accessing the Column property of the Range object. Commented Jul 31, 2018 at 21:53
  • 1
    Also, consider changing this line Set rng = Range("H2", "H" & LastRow) to Set rng = .Range("H2", "H" & LastRow) (note the . before range. This will mean you're always referring to the Active OoD sheet, even if it's not currently active. Commented Jul 31, 2018 at 21:58
  • Thank you, handy little tip that worked a treat Commented Sep 9, 2018 at 20:25

1 Answer 1

3

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
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you very much this fixed the problem (sorry for delayed acknowledgement, I got pulled onto another project)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.