sorting vba button

[harveya915]

Got a sheet with a list of inventory items on column "A". Every week I insert 5 new columns from C-G with information for that item such as how many were used, restocked, total items, price of item, and cost of restocking. I created a text box, that will help adding inventory to the sheet (button1 opens a UserForm with a text box, description gets put into text box, "OK" button is clicked and the new item gets inserted in to the last row of column A.) Is it possible for when the new item gets added for it be inserted in alphabetical order/ sorted? I know excel has a built in sort/filter function, however I'm not sure if it will keep working considering the fact that I keep adding columns to the sheet and when applying the filter drop down buttons you have to select the cells to which you want it added to.

Below is the code i have for adding the part:

Private Sub CommandButton6_Click()

Dim emptyRow As Long

'Make Sheet1 active
Sheet1.Activate

Worksheets("Sheet1").Unprotect Password:="2019"

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 2

'Transfer information
Sheet1.Cells(emptyRow, 1).Value = TextBox1.Value

Unload UserForm2

End Sub

 

[JPARKHURST]

Using VB you don't need to worry about adding extra columns.

After you add your values to the data above, then you can determine the range of your data, and sort from there. If you have filters turned on, you'll want to turn those off before your sort.

dim WORKSHEET as worksheet
dim RANGETOSORT as range
dim LASTROW as long, LASTCOLUMN as long

  set WORKSHEET = Sheets("WorksheetName")

  call ResetFilters(WORKSHEET)

  'sets LASTROW to last *uninterupted* populated row in column "A"
  LASTROW = WORKSHEET.Cells(Rows.Count, "A").End(xlUp).Row
  'sets LASTCOLUMN
  LASTCOLUMN = WORKSHEET.Cells(1,WORKSHEET.Columns.Count).End(xlToLeft).Column

  'Sets RangeToSort to A1:LastRow,LastColumn
  Set RANGETOSORT = WORKSHEET.Range(Cells(1,1), Cells(LASTROW, LASTCOLUMN))


  'sort rows based on COLUMN A, then COLUMN Q
  RANGETOSORT.Sort key1:=Range("A2:A" & LASTROW), order1:=xlAscending, _
  key2:=Range("Q2:Q" & LASTROW), order2:=xlAscending

Here is what I have to catch my filters. There may be a better way, but this has works so far for me. Call shown above.

Public Sub ResetFilters(wsFilter3030 As String)
    If wsFilter3030.FilterMode = True Then
        wsFilter3030.ShowAllData
    End If
End Sub

 

[harveya915]

A couple of question:
1) Is there a specific reason why i would be sorting based on column Q?
2) Where to I input these codes?

 

[JPARKHURST]

A couple of question:
1) Is there a specific reason why i would be sorting based on column Q?
2) Where to I input these codes?

1A) My sorts were examples, sorting two columns. You simply need to modify the code to sort by column "A", yes? Remove Q.

2) Just tag them inside your action, then attach your button. The variables are in all caps, to point out what they are specifically.

public sub SortIt()

dim ws1 as worksheet
dim rng1 as range
dim lr1 as long, lc1 as long

set ws1 = Sheets("Sheet1")

'sets lastRow - you already have this in your code, but...
lr1 = ws1.cells(rows.count, "A").End(clUp).Row
'sets last column
lc1 = ws1.Cells(1,ws1.columns.count).End(xlToLeft).Column

'set range
set rng1 = ws1.Range(Cells(1,1), Cells(lr1, lc1))

'sort rows
rng1.Sort key1:=Range("A2:A" & lr1), order1:=xlAscending

end sub

Double click on your button and it will bring up your editor, right? (assuming you have this since you posted some code). Plunk the code above in, then find your button code and put call SortIT().

i'm going to grab some coffee and will re-read this to make sure I didn't miss any details. Let me know if anything does not work for you. And - of course - don't forget to make a backup before you modify and test any code, there is no undo with VBA.

Jon

 

[JPARKHURST]

I quickly see i did miss something. After you set your worksheet, you can call your filter reset.

set ws1=...

'call reset filters here to make sure all is visible and ready to sort
Call resetFilters(ws1)

...rest of code...

 

[harveya915]

I don't know a smudge about VBA, let me try and dissect your instructions and figure it out. The little bit that I know has been with trial and error and reading. If I still need more help, I will let you know. Thanks!