VBA to sort a list alphabetically but exclude cells with "0"

ginkas

New Member
Joined
Apr 29, 2014
Messages
47
Hello.
I would like to use a macro to sort a list of names alphabeticcaly but exclude the cells that have 0.
I am using the following VBA. my cells start at C6 and are linked to another worksheet.

Sub Worksheet_Change()
On Error Resume Next
Range("C5").Sort Key1:=Range("C6"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub


thank you for your help
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
1. What do you mean by "exclude the cells that have 0"? Do you mean put all those cells at the bottom or top or something else?

2. Cells that have 0 don't sound like names. I'm guessing that means they are the result of a formula looking at a blank cell on the other worksheet so give us more details ..
- what is the name of the worksheet you want to sort on?
- what is the name of the other worksheet?
- tell us about how the names are "linked" eg the formula for, say, C6

3. What is changing on this sorting sheet that is going to trigger the Worksheet_Change event to do this sorting?
 

ginkas

New Member
Joined
Apr 29, 2014
Messages
47
I have a workbook called comissions. There, I have a sheet that has a list of names (the name could also be a number such as 15606). I have copied and pasted special this list in another workbook called clients. because in my original list I may add names from time to time, I pasted link to ths list, so every time I add a name in the list it gets added also in the "clients" file list. then I use the macro stated above and it sorts my list (on the second workbook-clients) but because some cells have zero value, they go on top of the sorted list.Is there a way to exclude them as they don t represent any name?





1. What do you mean by "exclude the cells that have 0"? Do you mean put all those cells at the bottom or top or something else?

2. Cells that have 0 don't sound like names. I'm guessing that means they are the result of a formula looking at a blank cell on the other worksheet so give us more details ..
- what is the name of the worksheet you want to sort on?
- what is the name of the other worksheet?
- tell us about how the names are "linked" eg the formula for, say, C6

3. What is changing on this sorting sheet that is going to trigger the Worksheet_Change event to do this sorting?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
I have a workbook called comissions. ... I have copied and pasted special this list in another workbook called clients.
It appears now that the two worksheets are in fact also in different workbooks, but you didn't answer all my questions. In particular, what are the answers to these ..
2. Cells that have 0 don't sound like names. I'm guessing that means they are the result of a formula looking at a blank cell on the other worksheet so give us more details ..
- what is the name of the worksheet you want to sort on?
- what is the name of the other worksheet?
- tell us about how the names are "linked" eg the formula for, say, C6

3. What is changing on this sorting sheet that is going to trigger the Worksheet_Change event to do this sorting?
4. Also please state which sheet the Worksheet_Change code is in.


.. I pasted link to ths list, so every time I add a name in the list it gets added also in the "clients" file list.
This needs further clarification - refer especially the third point of question 2 above.


I need to be able to replicate your setup precisely so that I can develop a suggestion.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,608
Messages
5,597,143
Members
414,129
Latest member
mr vba

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
Top