Sorting data validation with VBA

[ddoyle67]

I am pretty new to using macros in my workbook to try and speed up using it. I have a data validation list (C2:C97) called "Description" that has 66 permanent items (C2:C67) that are alphabetized in it and another 30 items (C68:C97) that are added to the list via input on another sheet. When those 30 items don't have an item in the cells, they do have a formula, which returns a 0. What I would like to do is have that group(C2:C97) to automatically re-alphabetize its self when one of those 30 blank cells gets new data but to ignore the blank cells and to keep them at the end of the list. I have a code to alphabetize the list but its only working manually and it puts all the blank or 0 cells at the top. Any help with writing this code would be appreciated. Thanks in advance

 

[DanteAmor]

which group should re-alphabetize
(C2:C67), (C68:C97) or (C2:C97)
You can put your code

 

[ddoyle67]

which group should re-alphabetize
(C2:C67), (C68:C97) or (C2:C97)
You can put your code

The entire group C2:C97. What I don't want to do is having to keep going down to say line 85,if that received a new item, and that item in it that starts with a D for example. Id like it to be with the D items in the permanent list when its is input. but leave the blank cells at the bottom. The Cells C68:C97 are not always used or filled when using the workbook sometimes a few are used or a lot depending on the project. hope that helps explain it better.

 

[DanteAmor]

I do not understand very well how you have your data.
Do you have fixed data (C2:C67), do they have a formula?
If the result of the formula is different from 0, you want the macro to convert those cells to values, that is, the formula no longer exists. If the formula equals 0, should the formula remain?

 

[ddoyle67]

I do not understand very well how you have your data.
Do you have fixed data (C2:C67), do they have a formula?
If the result of the formula is different from 0, you want the macro to convert those cells to values, that is, the formula no longer exists. If the formula equals 0, should the formula remain?

Yes the data in C2:C67 is fixed, no formula. This is an estimating set up. The data validation list (C2:C97) is for a sheet that lists the scope of work we would perform. I select the item from the list to add to the scope of work. These(C2:C67) are standard items we would make for a project. Many projects require different items are not standard that would be included in that data validation list to be chosen from the list on the scope sheet. The custom items would be entered in another sheet to price them out then the name for that item would added to the list(C68:97) via the formula, which can be then selected. The formula needs to stay in the cell (C68:C97) because that is how it gets populated, the formula pulls the name from the other sheet that calculates the price for the item. I want the remaining unpopulated cells of (C68:C97) to be remain at the bottom of the list. Right now since they are a 0 they get put at the top then followed by the alphabetical list.

 

[DanteAmor]

Sorry, but I still do not understand.
Example: if in cell C69 (with formula) you put the value "Beta", then you want to sort the data. You want to pass the data "Beta" between the data "Alpha" (C2) and "Gamma" (C3), but the formula in C69 remains intact. What happens with cells C4 to C67, if they are all full?
This is very ambiguous.

 

[ddoyle67]

Sorry, but I still do not understand.
Example: if in cell C69 (with formula) you put the value "Beta", then you want to sort the data. You want to pass the data "Beta" between the data "Alpha" (C2) and "Gamma" (C3), but the formula in C69 remains intact. What happens with cells C4 to C67, if they are all full?
This is very ambiguous.

Yes that is pretty much it. The empty cells are only filled once per estimate. Here is a link to a sheet I put the list one. I put 2 examples in the (C68:C97) section. When these are entered to the list they should be alphabetized into the C2:C67 section leaving the blank cells at the bottom of the list. when another custom item is added it then should be alphabetized into the list. So when the data validation drop down is opened you don't have to scroll down to line 80 to find the item there if its contents starts with the letter D.

https://1drv.ms/x/s!Are1kVUQg1qAg6Ax6memtIZddl9sYQ

 

[DanteAmor]

With the example I understand how your data is, but you could send another sheet with the expected result.

 

[ddoyle67]

Here is a new version with 3 more columns. The second column shows the items from the first resorted into the list in alphabetical order but leaving the blank or 0 cells where there were. The 3rd column adds 2 more items then the 4th shows them sorted back into the list alphabetically.


https://1drv.ms/x/s!Are1kVUQg1qAg6AyS_F7lg1E3x4j9A

 

[DanteAmor]

I put the macro to sort the data in column C.
The macro use column D temporarily to sort the data, then place it in column C already sorted.

I still do not understand is what will happen to the cell where there was a result, following your example, in cell C77 you have the data "Lockers". The macro orders the data, but what to do with the data of C77, according to your example disappears from cell C77, but what remains in its place, a formula? or do I go through the rows from bottom to top?


Try the macro and tell me.

Sub Macro2()
'
    Dim sh As Worksheet
    Dim j As Long, i As Long, f0 As Long
    Dim b As Range
    
    Application.ScreenUpdating = False
    Set sh = Sheets("Sheet1")
    sh.Range("D:D").ClearContents
    j = 2
    lr = sh.Range("C" & Rows.Count).End(xlUp).Row
    
    Set b = sh.Range("C:C").Find(0, LookIn:=xlValues, lookat:=xlWhole)
    If Not b Is Nothing Then
        f0 = b.Row
    Else
        f0 = lr + 1
    End If
    
    For i = lr To 2 Step -1
        If sh.Cells(i, "C").HasFormula Then
            If sh.Cells(i, "C").Value <> "" And sh.Cells(i, "C").Value <> 0 Then
                sh.Cells(j, "D").Value = sh.Cells(i, "C").Value
                j = j + 1
                sh.Cells(i, "C").Delete Shift:=xlUp
            End If
        Else
            If sh.Cells(i, "C").Value <> "" And sh.Cells(i, "C").Value <> 0 Then
                sh.Cells(j, "D").Value = sh.Cells(i, "C").Value
                j = j + 1
                If i > f0 Then
                    sh.Cells(i, "C").Delete Shift:=xlUp
                End If
            End If
        End If
    Next
    sh.Range("D2:D" & j - 1).Sort key1:=sh.Range("D2"), order1:=xlAscending, Header:=xlNo
    sh.Range("D2:D" & j - 1).Copy Range("C2")
    sh.Range("D:D").ClearContents
End Sub