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
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