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

Simply swap that line round like this

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

 

[dadanis]

Re: VBA code for a table - urgent help needed

Simply swap that line round like this

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

True legend, it works perfectly now!!! I will have to do this to many, many columns and the code copy pastes the figures in the A2:A11 range. I understand from your code why it copy pastes it into Col A but could you explain how can I adjust the rows where to copy paste, as I will need to do the same procedure for some additional columns and paste them below this data set i.e. in the range A12:A21? A big big thank you again, unbelievable that there are so kind people on this Planet!

 

[Fluff]

Re: VBA code for a table - urgent help needed

That line will always copy to the first blank cell.
The part in red

[COLOR=#ff0000]Range("A" & Rows.Count).End(xlUp)[/COLOR][COLOR=#0000ff].Offset(1)[/COLOR]

Starts at the very last row in the sheet & goes up until it finds a cell that is not empty. And then the part in blue offsets 1 row (ie moves down)

 

[dadanis]

Re: VBA code for a table - urgent help needed

That line will always copy to the first blank cell.
The part in red

[COLOR=#ff0000]Range("A" & Rows.Count).End(xlUp)[/COLOR][COLOR=#0000ff].Offset(1)[/COLOR]

Starts at the very last row in the sheet & goes up until it finds a cell that is not empty. And then the part in blue offsets 1 row (ie moves down)

Thanks for that when the column is completely empty this seems to be the case as everything copy pastes in the A2:A11 range, but as soon as I type something in, say, cell A3 for example ABC, the macro will copy paste the figures not in the A5:A14 range but A4:A13 range i.e. there will be no empty cell in between?