Help on Excel Worksheet Change Macro

Enuflogic

New Member
Joined
Feb 22, 2011
Messages
8
New to the forums, please be easy :)

I have an Excel worksheet change macro set up for data entered in a specific column to auto sort at a couple of different locations in the sreadsheet. It's set up like this:

CONCRETE
Name | Data (this cell has a named reference of CONCSTART to indicate start for sort function)
Name | Data
Name | Data

MASONRY
Name | Data (this cell has a named reference of MASSTART to indicate start for sort function)
Name | Data
Name | Data

When data is entered into the data column, it auto sorts ascending. I've used this before, and have set it up again, but here's my issue. My sections sort independently of each other, and I have about 10 or so. Some of the sections sort fine, others wont sort, but I'm not getting a VBA error, and all my scripts match syntax wise.

Here is the code I'm using:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Check that cell changed was in col H. If not, exit.
If Target.Column <> 8 Then Exit Sub

'Sorts Concrete cells ascending by bid amount
Range("CONCRETE").Sort Key1:=Range("CONSTART"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'Sorts Masonry cells ascending by bid amount
Range("MASONRY").Sort Key1:=Range("MASSTART"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Any thoughts?

I'm using Excel 2000, and the worksheet I am modifying was created in 2007.
 
Last edited:
I changed that and it works! Any idea why? What does that change reference? I got this formula about 2 years ago and forgot half of what went into it.

THANKS by the way! :biggrin:

Well if xl guesses there are headers when there aren't it will not include the top row in the sort.

BTW, if the only use for the ~~START named ranges is to identify the column to sort on, you can probably do away with them and replace them all with Range("H1").
 
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Well if xl guesses there are headers when there aren't it will not include the top row in the sort.

BTW, if the only use for the ~~START named ranges is to identify the column to sort on, you can probably do away with them and replace them all with Range("H1").

I use the ~~START named cells as a reference for the bottom as well to tabulate the apparent low bidders from each section to get a rough total, but thanks for the option.
 
Upvote 0

Forum statistics

Threads
1,224,600
Messages
6,179,835
Members
452,947
Latest member
Gerry_F

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