VBA code for a table

[dadanis]

Hi all,

I am using VBA today and I am struggling to put together a code that would work. Basically, I have huge table (B8:AH177) and I want VBA to sort the table in a descending order according to column S8 (S9:S177). Some of the data is not available, therefore it appears as #N/A in some cells and NR in other in the S8 column. First thing I want to do is unfilter these two variables so that they do not appear and order the actual figures ordered that are left after removing #N/A and NR in a descending order. I tried to write my own code and the two tasks work if I use the formulas separately but when I try to put them into one it stops working and an error message appears. Could someone please save me and show me my error? In addition, my code for removing #N/A and NR makes them disappear rather than unfilter, is there any way to actually unfilter these variables (i.e. when I press on the dropbox I would actually see that they are unfiltered). Hope this makes sense, thanks.

Sub huina()
'
ActiveSheet.Range("$B$8:$AH$177").AutoFilter Field:=17, Criteria1:="<>#N/A", Criteria2:="<>NR"
Operator:=xlFilterValues
'
Range("B8:AH8").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("CDS Analysis").AutoFilter .Sort.SortFields.Clear
ActiveWorkbook.Worksheets("CDS Analysis").AutoFilter.Sort.SortFields.Add Key _
:=Range("S8"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Headline CDS").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.Run "CheckLoadedAddins"
End Sub

 

[Fluff]

Re: VBA code for a table - urgent help needed

Hi & welcome to the board
Try

Range("$B$8:$AH$177").AutoFilter 17, "<>#N/A", xlAnd, "<>NR"

 

[dadanis]

Re: VBA code for a table - urgent help needed

Hi Fluff,

Thank you for your reply.

Unfortunately it still says Syntax error but what I forgot to mention is that it highlights "Operator:=xlOr" line in red light i.e. that's where the problem is I assume. Any idea why that could be the reason and how to fix it?

 

[Fluff]

Re: VBA code for a table - urgent help needed

There is no xlOr in either your code or mine. Could you please post the code you are actually using

 

[dadanis]

Re: VBA code for a table - urgent help needed

Ah I am so sorry my bad:

Sub huina()
'
ActiveSheet.Range("$B$8:$AH$177").AutoFilter Field:=17, Criteria1:="<>#N/A", Criteria2:="<>NR"
Operator:=xlFilterValues
'
Range("B8:AH8").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("CDS Analysis").AutoFilter .Sort.SortFields.Clear
ActiveWorkbook.Worksheets("CDS Analysis").AutoFilter.Sort.SortFields.Add Key _
:=Range("S8"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Headline CDS").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.Run "CheckLoadedAddins"
End Sub

 

[dadanis]

Re: VBA code for a table - urgent help needed

Sub huina()
'
ActiveSheet.Range("$B$8:$AH$177").AutoFilter Field:=17, Criteria1:="<>#N/A", Criteria2:="<>NR"
Operator:=xlFilterValues
'
Range("B8:AH8").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("CDS Analysis").AutoFilter .Sort.SortFields.Clear
ActiveWorkbook.Worksheets("CDS Analysis").AutoFilter.Sort.SortFields.Add Key _
:=Range("S8"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Headline CDS").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.Run "CheckLoadedAddins"
End Sub

 

[Fluff]

Re: VBA code for a table - urgent help needed

There is still no xlOr in that code, so can you please explain this comment

I forgot to mention is that it highlights "Operator:=xlOr"

 

[dadanis]

Re: VBA code for a table - urgent help needed

I tried to replace xlOr with xlFilterValues but neither work. Please see the initial version:

Sub huina()
'
ActiveSheet.Range("$B$8:$AH$177").AutoFilter Field:=17, Criteria1:="<>#N/A", Criteria2:="<>NR"
Operator:=xlOr
'
Range("B8:AH8").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("CDS Analysis").AutoFilter .Sort.SortFields.Clear
ActiveWorkbook.Worksheets("CDS Analysis").AutoFilter.Sort.SortFields.Add Key _
:=Range("S8"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Headline CDS").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.Run "CheckLoadedAddins"
End Sub

 

[Fluff]

Re: VBA code for a table - urgent help needed

Have you tried replacing those 1st 2 rows, with my suggestion in post#2?

 

[dadanis]

Re: VBA code for a table - urgent help needed

I tried and used the following code:

Sub huina()
'
Range("$B$8:$AH$177").AutoFilter 17, "<>#N/A", xlAnd, "<>NR"
'
Range("B8:AH8").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("CDS Analysis").AutoFilter .Sort.SortFields.Clear
ActiveWorkbook.Worksheets("CDS Analysis").AutoFilter.Sort.SortFields.Add Key _
:=Range("S8"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Headline CDS").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.Run "CheckLoadedAddins"
End Sub

Unfortunately after launching the macro it says: Compile error: Invalid or unqualified reference