Sorting using VBA

[sduray]

Hello,

I have another problem with the VBA code. This one was writtend by someone more knowledgeable than I. Unfortunately, I have been unable to utilize it. This code was written to allow me to sort my active worksheet by using a set of values. Basically, in the report I receive, in column O there are different values, and I am just trying to extract a subset of values that I need from it (I just want to hide the other values rather than deleting them if possible). This subset of values comes from a separate spreadsheet.

To summarize, in the active spreadsheet that I am working on formatting has 13 columns (active columns are a, b, c, d, e, f, o, p, r, x, ab, ax, ay). The reference spreadsheet has only one columnl--a.

However, when I run the following code I get a debug error that I don't know how to fix :

[VBA]Sub Name()
Dim x As Integer
Dim i As Integer
Dim y As Integer
Dim b As Integer
Dim z As String
Worksheets("Sheet 2").Select
Range("a2").Select
x = Range("A2", Range("a2").End(xlDown)).Cells.Count
Dim codes(10)
For i = 1 To x
codes(i) = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
MsgBox codes(i)
Next i
Worksheets("Sheet1").Select
Range("a2").Select
Do Until IsEmpty(ActiveCell)
For b = 1 To x
z = codes(b)
If ActiveCell.Value = codes(b) Then
y = y + 1
End If
Next b
Debug.Print y
If y = 0 Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
y = 0
Loop
End Sub[/VBA]

Thank you for your help,

SD

[Dave]

[vba] x = Range("A2", Range("a2").End(xlDown)).Cells.Count
Dim codes(10)
For i = 1 To x
codes(i) = ActiveCell.Value
[/vba]
Your error likely is that "x" may be larger (ie. more rows) than 10 which is what you dimmed your "x" to. So it errors on "codes(i) = ActiveCell.Value" as you are probably beyond the scope of your array. This won't hurt and it's just a guess. Dave
[vba] x = Range("A2", Range("a2").End(xlDown)).Cells.Count
Dim codes(x)
For i = 0 To (x -1)
codes(i) = ActiveCell.Value
[/vba]
...needs this too..
[vba]
For b = 0 To (x - 1)

[/vba]

[sduray]

Thank you for the suggestion. I have modified the code as you suggested Dave, but I'm still getting a run-time error '9'/Subscript Out of Range. When I got to debug, the yellow highligter is on the "Worksheets ("sheet1").Select row. Where do I go from here.

Here's how the modified code looks:
[VBA]Sub HideTRRCodesNotApplicable()
Dim x As Integer
Dim i As Integer
Dim y As Integer
Dim b As Integer
Dim z As String
Worksheets("sheet1").Select
Range("a2").Select
x = Range("A2", Range("a2").End(xlDown)).Cells.Count
Dim codes(10)
For i = 1 To x
codes(i) = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
MsgBox codes(i)
Next i
Worksheets("sheet2").Select
Range("a2").Select
Do Until IsEmpty(ActiveCell)
For b = 1 To x
z = codes(b)
If ActiveCell.Value = codes(b) Then
y = y + 1
End If
Next b
Debug.Print y
If y = 0 Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
y = 0
Loop
End Sub[/VBA]

[nst1107]

sduray, the error is most likely occurring there because you don't have a worksheet named "sheet1".

[Dave]

SD it doesn;t appear if you modified your code previously? Unless you're using option Base 1, which U ain't, then this is nb... you're not going to resolve this until U accomodate the zero position of the array (OR add option base to the starrt of your routine). Further, "codes" is not dimmed as an array variable (ie. Codes() as Variant works). SD U need to fix this 1st before fixing your problem. HTH. Dave