Automatically Sorting Data in Excel using VBA

M

Michele Ferrao

New Member
Joined
Sep 9, 2014
Messages
3
Hello

I would really appreciate your help in understanding how to automatically sort rows depending on the value in a certain column.
The data in the table is based on a Vlookup formula. The range of data including the headings is from B9 to K24. I want to sort the information in these cells depending on column C(Product Code) with values from C10 to C24 in a descending order automatically. Row 9 is the heading of the table.
Component Ingredients
Product Code
Weight Kg/L
%
Allergen, if present
Roast beef
35
0.100
28.33%
nil
Rock melon
#N/A
0.070
19.83%
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
Whole grain mustard
#N/A
0.040
11.33%
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
Melon
#N/A
0.070
19.83%
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
Tomato Wedge
#N/A
0.026
7.37%
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
Parmesan
#N/A
0.020
5.67%
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
Pesto
5
0.010
2.83%
nil
Diced tomato
#N/A
0.005
1.42%
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
Arugula
#N/A
0.005
1.42%
Sundried tomato
#N/A
0.004
1.13%
Lolorosso
#N/A
0.002
0.57%
Kalamata olive
#N/A
0.001
0.28%
Chive
#N/A
0.001
0.28%
Wheat bread
400206
1 slice
#VALUE!
#N/A
0.000
0.00%

<tbody>
</tbody>
I have tried the VBA’s codes provided in previous posts, but cannot amend it to suit my needs. Your help will be really appreciated.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Hi,

You can do this by using the RANK() formula. However the #N/A's may be a problem.
Will the actual data have these errors?

Code: 
[TABLE="width: 256"]
 <colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="class: xl63, width: 64"]Data[/TD]
  [TD="class: xl63, width: 64"]Rank [/TD]
  [TD="class: xl63, width: 64"][/TD]
  [TD="class: xl63, width: 64"]Result[/TD]
 [/TR]
 [TR]
  [TD="align: right"]40[/TD]
  [TD="align: right"]4[/TD]
  [TD][/TD]
  [TD="align: right"]10[/TD]
 [/TR]
 [TR]
  [TD="align: right"]50[/TD]
  [TD="align: right"]5[/TD]
  [TD][/TD]
  [TD="align: right"]20[/TD]
 [/TR]
 [TR]
  [TD="align: right"]80[/TD]
  [TD="align: right"]8[/TD]
  [TD][/TD]
  [TD="align: right"]30[/TD]
 [/TR]
 [TR]
  [TD="align: right"]30[/TD]
  [TD="align: right"]3[/TD]
  [TD][/TD]
  [TD="align: right"]40[/TD]
 [/TR]
 [TR]
  [TD="align: right"]20[/TD]
  [TD="align: right"]2[/TD]
  [TD][/TD]
  [TD="align: right"]50[/TD]
 [/TR]
 [TR]
  [TD="align: right"]10[/TD]
  [TD="align: right"]1[/TD]
  [TD][/TD]
  [TD="align: right"]60[/TD]
 [/TR]
 [TR]
  [TD="align: right"]60[/TD]
  [TD="align: right"]6[/TD]
  [TD][/TD]
  [TD="align: right"]70[/TD]
 [/TR]
 [TR]
  [TD="align: right"]70[/TD]
  [TD="align: right"]7[/TD]
  [TD][/TD]
  [TD="align: right"]80[/TD]
 [/TR]
 [TR]
  [TD="align: right"]90[/TD]
  [TD="align: right"]9[/TD]
  [TD][/TD]
  [TD="align: right"]90[/TD]
[/TR]
</tbody>[/TABLE]

Column B
=RANK(A2,A:A,1)

Column C
=INDEX(A:A,MATCH(SMALL(B:B,ROW()-1),B:B,FALSE),1)

I hope that helps
 
Upvote 0
.
.

Place this macro in your workbook and change as necessary:

Code: 
Sub SortData()

    'Change this line accordingly:
    With ThisWorkbook.Worksheets("Sheet1")
        
        .Range("B9").CurrentRegion.Sort _
            Key1:=.Range("C9"), _
            Order1:=xlDescending, _
            Header:=xlYes
        
    End With

End Sub
 
Upvote 0
Scrappy666 said:
Hi,

You can do this by using the RANK() formula. However the #N/A's may be a problem.
Will the actual data have these errors?

Code: 
[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Data
[/TD]
[TD="class: xl63, width: 64"]Rank
[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]Result
[/TD]
[/TR]
[TR]
[TD="align: right"]40
[/TD]
[TD="align: right"]4
[/TD]
[TD][/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD="align: right"]50
[/TD]
[TD="align: right"]5
[/TD]
[TD][/TD]
[TD="align: right"]20
[/TD]
[/TR]
[TR]
[TD="align: right"]80
[/TD]
[TD="align: right"]8
[/TD]
[TD][/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD="align: right"]30
[/TD]
[TD="align: right"]3
[/TD]
[TD][/TD]
[TD="align: right"]40
[/TD]
[/TR]
[TR]
[TD="align: right"]20
[/TD]
[TD="align: right"]2
[/TD]
[TD][/TD]
[TD="align: right"]50
[/TD]
[/TR]
[TR]
[TD="align: right"]10
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[TD="align: right"]60
[/TD]
[/TR]
[TR]
[TD="align: right"]60
[/TD]
[TD="align: right"]6
[/TD]
[TD][/TD]
[TD="align: right"]70
[/TD]
[/TR]
[TR]
[TD="align: right"]70
[/TD]
[TD="align: right"]7
[/TD]
[TD][/TD]
[TD="align: right"]80
[/TD]
[/TR]
[TR]
[TD="align: right"]90
[/TD]
[TD="align: right"]9
[/TD]
[TD][/TD]
[TD="align: right"]90
[/TD]
[/TR]
</tbody>[/TABLE]

Column B
=RANK(A2,A:A,1)

Column C
=INDEX(A:A,MATCH(SMALL(B:B,ROW()-1),B:B,FALSE),1)

I hope that helps
Click to expand...

Hi. This does not help me as I have N/A in my data.
 
Upvote 0
gpeacock said:
.
.

Place this macro in your workbook and change as necessary:

Code: 
Sub SortData()

    'Change this line accordingly:
    With ThisWorkbook.Worksheets("Sheet1")
        
        .Range("B9").CurrentRegion.Sort _
            Key1:=.Range("C9"), _
            Order1:=xlDescending, _
            Header:=xlYes
        
    End With

End Sub
Click to expand...

Hi. I have copy pasted this code, but it still doesn't work. Is there anything I can do differently?
 
Upvote 0
.
.

It works fine.

(1) Make sure you place the macro in a standard code module (i.e. not in ThisWorkbook, Sheet1, Sheet2, etc.) in the workbook containing your data;
(2) Make sure you include the Sub... End Sub statements at the beginning and end of the procedure respectively;
(3) Make sure you change the indicated line to the correspond to the name of the workheet containing your data; and
(4) Check your Macro Settings (in the Trust Center) to make sure that macros aren't completely disabled.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,653
Messages
6,120,751
Members
448,989
Latest member
mariah3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top