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

what gets highlighted?

 

[dadanis]

Re: VBA code for a table - urgent help needed

ActiveWorkbook.Worksheets("CDS Analysis").AutoFilter .Sort.SortFields.Clear

Word "Sort" highlighted in navy blue.

 

[Fluff]

Re: VBA code for a table - urgent help needed

That's because you have a space before the .Sort, which shouldn't be there.
Try removing it.

 

[dadanis]

Re: VBA code for a table - urgent help needed

That's because you have a space before the .Sort, which shouldn't be there.
Try removing it.

That worked, thank you very much. But now it says: "Object variable or With block variable not set"

ActiveWorkbook.Worksheets("CDS Analysis").AutoFilter.Sort.SortFields.Clear <-- This all gets highlighted in yellow.

 

[Fluff]

Re: VBA code for a table - urgent help needed

If CDS Analysis is the active sheet, then remove this

Selection.AutoFilter

 

[dadanis]

Re: VBA code for a table - urgent help needed

You are a God thank you so much I cannot express my gratitude in words haha really really thanks!

One more thing: this seem to have worked now, but again NR and #N/A have disappeared rather than unfiltered. Because of this reason, my gratest figure appears in cell S31 and next one in S34 (The figures in between are not available thus) but I want to create a further code copy paste top10 figures into a different table. what I was thinking to do is simply write a code to copy paste top 10 cells i.e. S9 to S18 but because of these gaps and changing starting figure this will not be possible. Do you have any suggestions to how to overcome this issue? Many thanks again.

 

[Fluff]

Re: VBA code for a table - urgent help needed

They haven't "disappeared" they've simply been filtered so as not to show.
Rather than using an autofilter sort, why not just do a normal sort, that way you can copy paste the top10 cells

 

[dadanis]

Re: VBA code for a table - urgent help needed

They haven't "disappeared" they've simply been filtered so as not to show.
Rather than using an autofilter sort, why not just do a normal sort, that way you can copy paste the top10 cells

Sorry I have to say that this is the first day I am using VBA therefore I do not understand all concepts! Could you please explain if by normal sort you are referring just to sort? I am asking because how could I remove #N/A and NR if I do not use filter?

 

[Fluff]

Re: VBA code for a table - urgent help needed

If you simply sort the data highest to lowest the NR & #N/A should end up at the bottom of the list, so you can then copy the top ten rows.

 

[dadanis]

Re: VBA code for a table - urgent help needed

If you simply sort the data highest to lowest the NR & #N/A should end up at the bottom of the list, so you can then copy the top ten rows.

Fluff, thank you for your help, but the issue is that #N/A and NR appear either at the top if you choose descending order or at the bottom if you choose ascending order. as I am using live feed data, the amount of N/A data is constantly changing. At this point I get the top 10 movements in order but the issue is that when I click on the filter neither #N/A nor NR appear on the list. Therefore my highest figure appears in cell S31 rather than S9 where I want it to be so that my macro would automatically copy paste range S9:S18 for the top 10 figures. the largest figure is in cell S31 because there are 30 #N/A figures but this will vary across time and next time there might be only 5 #N/A figures therefore range S31:s40 won't represent the top 10 anymore. Any idea how to make the macro 'unselect' #N/R and NR from the drop-down box or any other way to solve this issue?