VBA Code is not sorting every cell on a range/list from A2:A1000

[Splifford1]

Hi!

I set up the code (pasted below) to sort a product list but if I add a blank space in the column between names it will not sort any cell after the first blank cell in the column!How can I make the code to sort everything from A2:B1000?
This is causing that my combobox which extracts the info from the a 'name manager' product list to not have all of the names that appear below the first blank cell.

A1 and B1 are headers
A2 and B2 all the way to A1000 AND B1000 is the list designed space.

Sub GOBACKTOMENUFROMPRODUCTDB()Application.ScreenUpdating = False
    Application.Calculation = xlManual
    Hoja5.Unprotect Password:="...."
    
[B]    Set rng2 = Sheet5.Range("A2:A1000")[/B]
[B]       With Sheet5.Sort[/B]
[B]        .SortFields.Clear[/B]
[B]        .SortFields.Add Key:=rng2, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal[/B]
[B]        .SetRange Hoja5.Range("A1").CurrentRegion[/B]
[B]        .Header = xlYes[/B]
[B]        .MatchCase = False[/B]
[B]        .Orientation = xlTopToBottom[/B]
[B]        .SortMethod = xlPinYin[/B]
[B]        .Apply[/B]
[B]    End With[/B]
    
'THE CODE BELOW IS JUST TO COPY THE BASIC EXCEL CELL FORMAT FROM A HIDDEN CELL TO THE LIST ITEMS TO MAKE IT STANDARDIZED.
''
            Range("S3").Select
    Selection.Copy
    Range("A2:A1000").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False


    Range("T3").Select
    Selection.Copy
    Range("B2:B1000").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
  '''''
  
    Sheet7.Visible = True
    ActiveSheet.Visible = False
    Sheet7.Select


    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollRow = 1
    Sheet5.Protect Password:="....", AllowSorting:=True, AllowFiltering:=True
 Application.ScreenUpdating = True
     Application.Calculation = xlAutomatic
End Sub

 

[Peter_SSs]

Should it be this?

<del>.SetRange Hoja5.Range("A1").CurrentRegion</del>
.SetRange rng2.resize(,2)

 

[Splifford1]

That works but.. the combobox1 list is not increasing with new input cells from the original amount of cells it had filled (112)
If I type in any new cells below in the original list, the combobox1 is not adding these new cells

This is the formula of name manager UserList that the combobox1 reads from, is it the problem? =OFFSET(BDCamiones!$A$2,0,0,COUNTA(BDCamiones!$A:$A)-1)

 

[Peter_SSs]

Can't tell for sure as I am not looking at your actual file but if you have blanks in the data, your formula in the name manager will be missing entries. For example, with the data below, COUNTA will give 6, so your OFFSET will return a range from A2 of height 6-1. That is, the range A2:A6, clearly omitting Item 4 and Item 5.

Perhaps you should be trying to eliminate the blanks in that list?

Book1
	A
1	List
2	Item 1
3
4	Item 2
5	Item 3
6
7	Item 4
8
9	Item 5
BDCamiones

 

[Splifford1]

There are no blanks anymore thanks to your code implementation!
the problem now is that the combobox displaying that list is somehow not adding new iterations or modifications that are bigger than the current list

 

[Peter_SSs]

Working with ComboBoxes is not a particular strength of mine. However, you might get more help if you specify whether it is on a UserForm or directly in a worksheet, a Forms Control ComboBox or and ActiveX ComboBox, what you have, where in your workbook, what you are trying to achieve and how, post any vba code that is already implemented etc. The more information you give, the more likely somebody will pick it up and offer suggestions.