Auto Sort When Data Changes

%3CLINGO-SUB%20id%3D%22lingo-sub-1600401%22%20slang%3D%22en-US%22%3EAuto%20Sort%20When%20Data%20Changes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1600401%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20building%20an%20excel%20spreadsheet%20to%20track%20my%20investments%20and%20I%20have%20the%20spreadsheet%20ordered%20based%20on%20overall%20%25%20of%20my%20portfolio%2C%20where%20the%20largest%20position%20is%20at%20the%20top%20and%20smallest%20at%20the%20bottom.%20My%20goal%20is%20to%20have%20it%20automatically%20sort%20(from%20largest%20to%20smallest)%20when%20data%20is%20updated%20along%20with%20the%20other%20corresponding%20rows.%20I%20also%20don't%20want%20any%20of%20the%20formulas%20in%20the%20corresponding%20cells%20to%20get%20messed%20up.%20I%20can%20go%20to%20the%20Sort%20%26amp%3B%20Filter%20option%20on%20the%20home%20ribbon%20but%20I%20don't%20want%20to%20have%20to%20do%20that%20manually%20every%20time%20the%20data%20changes.%20Thanks%20in%20advance.%20This%20seems%20like%20it%20should%20be%20easier%20than%20it%20is.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1600401%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1600543%22%20slang%3D%22de-DE%22%3ESubject%3A%20Auto%20Sort%20When%20Data%20Changes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1600543%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F765239%22%20target%3D%22_blank%22%3E%40webbsinferno%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20be%20helpful%20if%20a%20file%20always%20attached%20to%20the%20questions%20asked%20(without%20sensitive%20data)%2C%20so%20that%20you%20can%20reach%20your%20goal%20faster%20and%20we%20will%20answer%20faster.%20a%20win-win%20situation.%20So%20you%20have%20to%20guess%20...%20Personally%20I%20was%20never%20good%20at%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20still%20try%20to%20help%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3EHere%20is%20a%20macro%20as%20an%20example.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0ARange(%22A2%3AI32%22).Sort%20Key1%3A%3DRange(%22D8%3AD32%22)%2C%20Order1%3A%3DxlDescending%2C%20Header%3A%3DxlGuess%2C%20OrderCustom%3A%3D1%2C%20MatchCase%3A%3DFalse%2C%20Orientation%3A%3DxlTopToBottom%2C%20DataOption1%3A%3DxlSortNormal%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20find%20out%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1600555%22%20slang%3D%22de-DE%22%3ESubject%3A%20Auto%20Sort%20When%20Data%20Changes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1600555%22%20slang%3D%22de-DE%22%3ESorry%20previous%20VBA%20macro%20is%20for%20date%20cells.%20For%20this%20%2F%20with%20cell%20range%20selection%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Private%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%20%3CBR%20%2F%3E%20If%20Not%20Intersect(Target%2C%20Range(%22A5%3AA300%22))%20Is%20Nothing%20Then%20%3CBR%20%2F%3E%20Range(%22A5%3AG300%22).%20Sort%20Key1%3A%3DRange(%22A5%22)%2C%20_%20%3CBR%20%2F%3E%20Order1%3A%3DxlAscending%2C%20_%20%3CBR%20%2F%3E%20Header%3A%3DxlNo%20%3CBR%20%2F%3E%20End%20If%20%3CBR%20%2F%3E%20End%20Sub%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello, 

 

I'm building an excel spreadsheet to track my investments and I have the spreadsheet ordered based on overall % of my portfolio, where the largest position is at the top and smallest at the bottom. My goal is to have it automatically sort (from largest to smallest) when data is updated along with the other corresponding rows. I also don't want any of the formulas in the corresponding cells to get messed up. I can go to the Sort & Filter option on the home ribbon but I don't want to have to do that manually every time the data changes. Thanks in advance. This seems like it should be easier than it is. 

2 Replies

@webbsinferno 

It would be very helpful if a file always attached to the questions asked (without sensitive data), so that you can reach your goal faster and we will answer faster. a win-win situation. So you have to guess ... personally I was never good at that.

 

I will still try to help

Here is a macro as an example.

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A2:I32").Sort Key1:=Range("D8:D32"), Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End Sub

 

I would be happy to find out if I could help.

 

Nikolino

I know I don't know anything (Socrates)

Sorry previous VBA macro is for date cells. For this / with cell range selection

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A5:A300")) Is Nothing Then
Range("A5:G300").Sort Key1:=Range("A5"), _
Order1:=xlAscending, _
Header:=xlNo
End If
End Sub