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

Does this do what you want?

   With Worksheets("CDS Analysis").Range("$B$8:$AH$177")
      .AutoFilter 17, "<>#N/A", xlAnd, "<>NR"
      .AutoFilter Field:=18, Criteria1:="10", Operator:=xlTop10Items
      .Offset(1).SpecialCells(xlVisible).Copy Sheets("[COLOR=#ff0000]Test1[/COLOR]").Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
   End With
   Application.Run "CheckLoadedAddins"
End Sub

It will copy the top 10 filtered values to a sheet called test1, change as needed

 

[dadanis]

Re: VBA code for a table - urgent help needed

It filters but does not copy paste and shows the following error:

"Autofilter method of Range class failed" and highlights the following in yellow:

.AutoFilter Field:=18, Criteria1:="10", Operator:=xlTop10Items

 

[Fluff]

Re: VBA code for a table - urgent help needed

What do you have in col S?

 

[dadanis]

Re: VBA code for a table - urgent help needed

What do you have in col S?

Weekly % Change of the share prices expressed in percentages - some data is not available, hence #N/A and NR. Again, this is a live feed.

 

[Fluff]

Re: VBA code for a table - urgent help needed

Does this do what you want?

Sub chk()
   Dim Rng As Range
   Dim Cnt As Long
   
   With Worksheets("[COLOR=#ff0000]MA (2)[/COLOR]")
      .Range("$B$8:$AH$177").AutoFilter 18, "<>#N/A", xlAnd, "<>NR"
      .AutoFilter.Sort.SortFields.Clear
      .AutoFilter.Sort.SortFields.Add Key _
         :=Range("S8"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
         :=xlSortNormal
      With .AutoFilter.Sort
         .header = xlYes
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
      End With
      For Each Rng In .Range("B9:B177").SpecialCells(xlVisible)
         Cnt = Cnt + 1
         Rng.EntireRow.Copy Sheets("[COLOR=#ff0000]Test1[/COLOR]").Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
         If Cnt = 10 Then Exit For
      Next Rng
   End With
   Application.Run "CheckLoadedAddins"
End Sub

Change sheet names in red to suit

 

[dadanis]

Re: VBA code for a table - urgent help needed

You sir are true legend! It all works nicely, just one last question (SORRY FOR THIS): it copy pastes top 10 figures for all columns rather than only column S, could you please adjust it so that it copy pastes only column S?

 

[Fluff]

Re: VBA code for a table - urgent help needed

Try

      For Each Rng In .Range("S9:S177").SpecialCells(xlVisible)
         Cnt = Cnt + 1
         Rng.Copy Sheets("Test1").Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
         If Cnt = 10 Then Exit For
      Next Rng

 

[dadanis]

Re: VBA code for a table - urgent help needed

It shows no errors but unfortunately no data is copy pasted at all anymore from CDS analysis into Test1

Here is the code:

Sub soreiz()
   Dim Rng As Range
   Dim Cnt As Long
   
   With Worksheets("CDS Analysis")
      .Range("$B$8:$AH$177").AutoFilter 18, "<>#N/A", xlAnd, "<>NR"
      .AutoFilter.sort.SortFields.Clear
      .AutoFilter.sort.SortFields.Add Key _
         :=Range("S8"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
         :=xlSortNormal
      With .AutoFilter.sort
         .Header = xlYes
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
      End With
    For Each Rng In .Range("S9:S177").SpecialCells(xlVisible)
         Cnt = Cnt + 1
         Rng.Copy Sheets("Test1").Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
         If Cnt = 10 Then Exit For
      Next Rng
   End With
   Application.Run "CheckLoadedAddins"
End Sub

 

[Fluff]

Re: VBA code for a table - urgent help needed

My mistake it should be

         Rng.Copy Sheets("Test1").Range("A" & Rows.Count).End(xlUp).Offset(1)

 

[dadanis]

Re: VBA code for a table - urgent help needed

My mistake it should be

         Rng.Copy Sheets("Test1").Range("A" & Rows.Count).End(xlUp).Offset(1)

Thanks for that, it seems that it will work, but because it is a live feed derived from formulas I need to paste special "values" because I get #REF ! if I do a simple copy paste. I know I owe you my life by now, but could you please adjust this in the formula?