Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
select range of cells in multiple colums automaticlly
i found this code but i cant figure out how to make it work for multiple
columns for example on this sheet. name Date charlie 7-27-07 zach 8-20-07 Private Sub Worksheet_Change(ByVal Target As Range) Dim MyData As String Dim OldData As Range Dim NewData As Range Dim Nms, r As Long 'Determine rangename containing target Set Nms = ActiveWorkbook.Names For r = 1 To Nms.Count If Not Intersect(Target, Range(Nms(r))) Is Nothing Or _ Not Intersect(Target.Offset(-1), Range(Nms(r))) Is Nothing Then MyData = Nms(r).Name Exit For End If Next Set OldData = Range(MyData) 'Check if target is in existing range or cell below If Not Intersect(Target, OldData) Is Nothing Then Set NewData = OldData 'In existing range ElseIf Not Intersect(Target, OldData.Offset(1)) Is Nothing Then Set NewData = Union(OldData, OldData.Offset(1)) 'Below existing range Else Exit Sub 'Not in data region End If 'Sort Data NewData.Sort Key1:=NewData.Cells(1), Order1:=xlAscending, Key2:=NewData.Cells(1), Order2:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 'Reset range name ActiveWorkbook.Names.Add Name:=MyData, RefersTo:="=" & ActiveSheet.Name & "!" & _ Range(NewData.Cells(1), NewData.Cells(1).End(xlDown)).AddressLocal Set Nms = Nothing Set OldData = Nothing Set NewData = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
select range of cells in multiple colums automaticlly
My Goal is to sort the range of cells when the user form is closed after
adding new entries. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
all cells in 2 workbooks have automaticlly changed to date format | Excel Worksheet Functions | |||
Select multiple adjacent cells of multiple cells without selecting | New Users to Excel | |||
Select multiple adjacent cells of multiple cells without selecting | Excel Worksheet Functions | |||
My Auto Sum Function is not Automaticlly summing the cells | Excel Worksheet Functions | |||
Compare a selected Range with a Named range and select cells that do not exist | Excel Programming |