Really simple, but really frustrating me... vba coding for single column sort

craigo

New Member
Joined
Aug 29, 2006
Messages
17
I'm VERY inexperienced with VBA, but trying to build a simple spreadsheet to facilitate data entry and management for my sister.
Basically, it's a 12 column contacts data . The userform is working nicely for the data entry, and data lookups. I expect to have a data modification (change addresses, preferences, etc.) up and running by the end of the weekend. All the work is done on a single sheet.
My problem is that I have a list box which presents the source of the contact, such as Facebook, church, mailing, etc.) A new source can be added to the bottom of the list that supports the list box. All this works really well, for a guy who doesn't know what he's doing. Unfortunately, I want more... I want to sort that list which in Cell BA1 and down.
The data has a header (Contact Source) which must remain at the top. I have read about a hundred pages of VBA coding online on this topic, copying and pasting the code when possible. No matter how I try to edit things, I continue to get one of several errors when I get to the sorting function.
I would REALLY appreciate any proper coding help that can be offered.
This is the coding I have gotten to so far. As I said, it all works well up to the sort command. I have used so many options offered by the web sites I visited, I have no idea what belongs in the code and what doesn't.

Private Sub AddSource_Click()

Dim NewSource As Range
Dim SortRange As Range

Worksheets("list").Activate
Set SortRange = Range(("ba2"), Range("ba2").End(xlDown))
Set NewSource = Worksheets("list").Range("ba1").End(xlDown).Offset(1, 0)

NewSource.Value = InputBox("Add new contact source", "New Contact Source", "Enter name of venue or contact source here.")

SortRange.Select
Set SortRange = Range(("ba1"), Range("ba2").End(xlDown))

SortRange.Select
Range.Sort.Range(("ba1"), Range("ba1").End(xlDown)), Header:=xlYes

Thank you all in advance.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Replace
SortRange.Select
Set SortRange = Range(("ba1"), Range("ba2").End(xlDown))

SortRange.Select
Range.Sort.Range(("ba1"), Range("ba1").End(xlDown)), Header:=xlYes
with
VBA Code:
With Worksheets("list").Range("BA1:BA" & Cells(Rows.Count, "BA").End(xlUp).Row)
    .Sort Key1:=Range("BA1"), order1:=xlAscending, Header:=xlYes
End With
 
Upvote 0
Solution
Sorry it took me so long to respond. There have been gremlins at every turn on this project, and I got wrapped up in a few of them before I could get to this.
Thank you so much for taking the time to hear my problem and write the code. I didn't check this forum for a few hours after I submitted this issue, but you had responded very quickly. With Facebook, email scams, and a host of other things, the internet sometimes gets a bad rep. It is refreshing to know that there are people like you in all the many forums across the web that are willing to help out another human being, rather than part of the hate, meanness, deception, etc. that permeates my screen. Thank you again.
 
Upvote 0
Although your query has already been answered, you might like this tip.

What I do is turn record macro ON, the do whatever it is that I want manually like sort etc. Then turn record macro OFF
I then have a look at the macro that was recorded and cut & paste that code into my code.
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

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
Back
Top