VBA Sort problem with existing code

[JMC57]

I am using the following VBA Code. Colum A in the sheet has a formula that pulls data from another sheet that is sorted via a VBA code on value in a different column . The problem is that this code does not sort at top due to zeros in blank cells. Is there something that needs to be added to correct this issue? </SPAN>

Private Sub Worksheet_Change(ByVal Target As Range)</SPAN>
If Intersect(Target, Range("a2:l6000")) Is Nothing Then Exit Sub</SPAN>
Application.EnableEvents = False</SPAN>
Me.sort.SortFields.Clear</SPAN>
Me.sort.SortFields.Add Key:=Range("a2:a6000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal</SPAN>
With Me.sort</SPAN>
.SetRange Range("A2:l6000")</SPAN>
.Header = xlGuess</SPAN>
.MatchCase = False</SPAN>
.Orientation = xlTopToBottom</SPAN>
.SortMethod = xlPinYin</SPAN>
.Apply</SPAN>
End With</SPAN>
Application.EnableEvents = True</SPAN>
End Sub</SPAN>

 

[Jerry Sullivan]

Hi JMC57,

Do you want to display Zeros in the "blank cells" or is that just showing up by default?
Those can be turned off in your Options if the cells are actually blank (empty).

Do you have formulas in the cells you are sorting (including the "blanks") or are they all constant values?

Are the "blanks" interspersed with the values or all together under your data? (before you run the sort macro)

You'll probably be better off having VBA find the actual data range instead of using an oversized range like a2:l6000.
Also, I'd suggest being explicit with Header = xlYes or Header = xlNo instead of .Header = xlGuess

 

[JMC57]

Hi JMC57,

Do you want to display Zeros in the "blank cells" or is that just showing up by default?
Those can be turned off in your Options if the cells are actually blank (empty).

Do you have formulas in the cells you are sorting (including the "blanks") or are they all constant values?

Are the "blanks" interspersed with the values or all together under your data? (before you run the sort macro)

You'll probably be better off having VBA find the actual data range instead of using an oversized range like a2:l6000.
Also, I'd suggest being explicit with Header = xlYes or Header = xlNo instead of .Header = xlGuess

HI JS

The zeros show up by default. There are formulas in these cells. I changed in options and the issue continues.</SPAN>
With the formulas there are no blanks.</SPAN>
I limited the range and changed the explicit as you suggested.</SPAN>
If I remove the formulas and enter the data manually (this is only an alpha sort) then the sort works. The problem is that the Zero is being given priority in the sort. forcing the sort to place zero first.</SPAN>

Thanks

 

[Jerry Sullivan]

What is the formula you are using in these cells?

Sorting cells that contain formulas instead of constants can be tricky if the fomulas use Relative Referencing instead of Absolute because the formula changes as the cell holding it is moved.

Regarding the blanks, you can probably revise your formulas to return a nullstring "" instead of a 0 if the result is to be "blank".
I can help with that syntax once I see your formula(s).

 

[JMC57]

What is the formula you are using in these cells?

Sorting cells that contain formulas instead of constants can be tricky if the fomulas use Relative Referencing instead of Absolute because the formula changes as the cell holding it is moved.

Regarding the blanks, you can probably revise your formulas to return a nullstring "" instead of a 0 if the result is to be "blank".
I can help with that syntax once I see your formula(s).

RX Item Desc</SPAN></SPAN>

Strength Description</SPAN></SPAN>

Cumulative % of Total Spend For Period</SPAN></SPAN>

Item Classification</SPAN></SPAN>

False Pict </SPAN> Beginning Inventory</SPAN></SPAN> <TBODY> </TBODY>

Package Size</SPAN></SPAN>

Quantity Purchase in Period</SPAN></SPAN>

False Pict </SPAN> Ending Inventory As a Multplier of Package Size</SPAN></SPAN> <TBODY> </TBODY>

Usage In Period </SPAN></SPAN>

Number of DAYS in Period</SPAN></SPAN>

DAILYUsage</SPAN></SPAN>

Max Days On Hand</SPAN></SPAN>

Max Shelf Quantity </SPAN></SPAN>

Minimum Quantity On Hand </SPAN></SPAN>

Safety Stock</SPAN></SPAN>

PAR Level MIN</SPAN></SPAN>

='2Cut And Paste Bob J'!C2</SPAN></SPAN>

='2Cut And Paste Bob J'!B2</SPAN></SPAN>

='2Cut And Paste Bob J'!J2</SPAN></SPAN>

='2Cut And Paste Bob J'!K2</SPAN></SPAN>

='2Cut And Paste Bob J'!F2</SPAN></SPAN>

='2Cut And Paste Bob J'!G2</SPAN></SPAN>

=E2+G2-H2</SPAN></SPAN>

='1Parameters'!I$2</SPAN></SPAN>

=I2/J2</SPAN></SPAN>

='1Parameters'!$I$4</SPAN></SPAN>

=(K2*L2)+P2</SPAN></SPAN>

=K2*'1Parameters'!$G$4</SPAN></SPAN>

=K2*'1Parameters'!$E$4</SPAN></SPAN>

=N2+O2</SPAN></SPAN>

='2Cut And Paste Bob J'!C3</SPAN></SPAN>

='2Cut And Paste Bob J'!B3</SPAN></SPAN>

='2Cut And Paste Bob J'!J3</SPAN></SPAN>

='2Cut And Paste Bob J'!K3</SPAN></SPAN>

='2Cut And Paste Bob J'!F3</SPAN></SPAN>

='2Cut And Paste Bob J'!G3</SPAN></SPAN>

=E3+G3-H3</SPAN></SPAN>

='1Parameters'!I$2</SPAN></SPAN>

=I3/J3</SPAN></SPAN>

='1Parameters'!$I$4</SPAN></SPAN>

=(K3*L3)+P3</SPAN></SPAN>

=K3*'1Parameters'!$G$4</SPAN></SPAN>

=K3*'1Parameters'!$E$4</SPAN></SPAN>

=N3+O3</SPAN></SPAN>

<TBODY>
</TBODY>

The cells with the abslule problem is The formula is ='sheet1'!C1 copied down to ='sheet1'!C6000


Thanks for you efforts!!</SPAN>

 

[Jerry Sullivan]

Your current approach is causing some difficulties and I'd suggest you revisit that.

Trying to sort data in which the key field has relative row references is very problematic.
Many of the other fields have relative row references also, so even if you could correctly sort Column A, most your other columns' data will get out of alignment with Column A.
And the worksheet change code that you are trying to write would imply that you are either adding/deleting or modifying values in Column A; which creates an inconsistent and complicated data environment of mixing constants and formula.

You might be better off with an approach that more cleanly separates "raw data", "user inputs", and "reporting"

An example of this is a PivotTable or Query Table that takes the raw data, summarizes it, and allows the user to filter or sort the report to show different information.
Alternatively you could use VBA to get some user inputs, Copy-Paste the raw data in another sheet, then manipulate the data into your desired report (including sorting, applying formulas and formatting).

If you are going to use formula references back to your source data; you might do so in a way that allows the sorting of your Key Column (by pasting values or using an Array formula), then use Lookup formulas to find the corresponding data for your other columns.

Sorry that I don't have an easier fix to suggest.

 

[JMC57]

Your current approach is causing some difficulties and I'd suggest you revisit that.

Trying to sort data in which the key field has relative row references is very problematic.
Many of the other fields have relative row references also, so even if you could correctly sort Column A, most your other columns' data will get out of alignment with Column A.
And the worksheet change code that you are trying to write would imply that you are either adding/deleting or modifying values in Column A; which creates an inconsistent and complicated data environment of mixing constants and formula.

You might be better off with an approach that more cleanly separates "raw data", "user inputs", and "reporting"

An example of this is a PivotTable or Query Table that takes the raw data, summarizes it, and allows the user to filter or sort the report to show different information.
Alternatively you could use VBA to get some user inputs, Copy-Paste the raw data in another sheet, then manipulate the data into your desired report (including sorting, applying formulas and formatting).

If you are going to use formula references back to your source data; you might do so in a way that allows the sorting of your Key Column (by pasting values or using an Array formula), then use Lookup formulas to find the corresponding data for your other columns.

Sorry that I don't have an easier fix to suggest.

Thank you for all of your efforts!