Reputation: 23
I have this code to eliminate all anomalies in the denormlaized hierarchy table. When I try to run this macro with a hundred rows of records, maybe around 200 to 300, it works just fine. But when I try to run the macro with all of my rows, which is around 18,000 lines, it returns the "Subscript out of range" error. I'm not sure what's wrong with the code 'cause it seems to work fine with hundreds of rows. I'm using MS Excel 2010 version. Any help would be appreciated, thank you very much.
Here's my working code:
Option Explicit
Sub EliminateAnomaliesDH()
Sheets("Denorm Hier").Select
Range("A1").Select
Dim iCtr As Integer
Dim arr As Variant
iCtr = 2
While Range("B" & iCtr).Value <> ""
arr = Split(Range("B" & iCtr).Value, "[")
arr = Split(arr(1), "]")
Select Case arr(0)
Case "L1"
Range("F" & iCtr & ":AB" & iCtr & "").Value = ""
Case "L2"
Range("H" & iCtr & ":AB" & iCtr & "").Value = ""
Case "L3"
Range("J" & iCtr & ":AB" & iCtr & "").Value = ""
Case "L4"
Range("L" & iCtr & ":AB" & iCtr & "").Value = ""
Case "L5"
Range("N" & iCtr & ":AB" & iCtr & "").Value = ""
Case "L6"
Range("P" & iCtr & ":AB" & iCtr & "").Value = ""
Case "L7"
Range("R" & iCtr & ":AB" & iCtr & "").Value = ""
Case "L8"
Range("T" & iCtr & ":AB" & iCtr & "").Value = ""
Case "L9"
Range("V" & iCtr & ":AB" & iCtr & "").Value = ""
Case "L10"
Range("X" & iCtr & ":AB" & iCtr & "").Value = ""
Case "L11"
Range("Z" & iCtr & ":AB" & iCtr & "").Value = ""
Case "L12"
Range("AB" & iCtr & ":AB" & iCtr & "").Value = ""
End Select
iCtr = iCtr + 1
Wend
Sheets("Instructions").Select
MsgBox "Successfully removed all anomalies of the Denormalized hierarchy Table"
End Sub
Upvotes: 2
Views: 3812
Reputation: 149335
Even though you have not mentioned the line where you are getting an error, it is quite obvious. The error is most probably on the line
arr = Split(arr(1), "]")
And the reason is very simple. Because the cell doesn't have "[" so after split there is no ar(1)
.
Here is a very simple way to reproduce the error.
Sub sample()
Dim sString As String
Dim myar
sString = "Blah Blah"
myar = Split(sString, "]")
myar = Split(myar(1), "[") '<~~ Error here
Debug.Print myar(0)
End Sub
To ensure that you don't get the error, use INSTR()
to check if [
or ]
exists and then split it.
For example
If InStr(1, sString, "]") Then
myar = Split(sString, "]")
End If
Followup from comments
I rewrote your code. Is this what you are trying? Please note that I have not tested it so let me know if you get any errors. I have commented the code on the relevant parts as well.
Sub EliminateAnomaliesDH()
Dim ws As Worksheet
Dim lRow As Long, i As Long
Dim tempString As String, sString As String
Set ws = ThisWorkbook.Sheets("Denorm Hier")
With ws
'~~> Get the last row which has data in Col B
lRow = .Range("B" & .Rows.Count).End(xlUp).Row
'~~> Loop through cells in column B
For i = 2 To lRow
sString = .Range("B" & i).Value
'~~> Check if the cell has both "[" and "]"
If InStr(1, sString, "[") And InStr(1, sString, "]") Then
tempString = Split(.Range("B" & i).Value, "[")(1)
tempString = Split(tempString, "]")(0)
'~~> This required so that we do an exact match
'~~> For example, " l1", " l1 ", " L1" etc
'~~> becomes "L1"
tempString = UCase(Trim(tempString))
Select Case tempString
Case "L1": .Range("F" & i & ":AB" & i & "").ClearContents
Case "L2": .Range("H" & i & ":AB" & i & "").ClearContents
Case "L3": .Range("J" & i & ":AB" & i & "").ClearContents
Case "L4": .Range("L" & i & ":AB" & i & "").ClearContents
Case "L5": .Range("N" & i & ":AB" & i & "").ClearContents
Case "L6": .Range("P" & i & ":AB" & i & "").ClearContents
Case "L7": .Range("R" & i & ":AB" & i & "").ClearContents
Case "L8": .Range("T" & i & ":AB" & i & "").ClearContents
Case "L9": .Range("V" & i & ":AB" & i & "").ClearContents
Case "L10": .Range("X" & i & ":AB" & i & "").ClearContents
Case "L11": .Range("Z" & i & ":AB" & i & "").ClearContents
Case "L12": .Range("AB" & i & ":AB" & i & "").ClearContents
End Select
End If
Next i
End With
MsgBox "Successfully removed all anomalies of the Denormalized hierarchy Table"
End Sub
Upvotes: 1