PDA

View Full Version : Input Box to Sort by row



elsone31
07-28-2015, 07:39 PM
I need a sort button attached to a macro that uses an input box to sort by the input value. The sort will always need to start with column E and the row number put into the input box. Any help is greatly appreciated. File attached.

elsone31
08-14-2015, 06:38 AM
I was able to get a code written do do this.

Private Sub SortbyRow_Click()


Dim myRng As Range
'
'Use an Input box to select the row to sort
myvalue = InputBox("Row Number")



Columns("E:FA").Select
ActiveWorkbook.Worksheets("Campus Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Campus Data").Sort.SortFields.Add Key:=Range( _
"E" & myvalue & ":FA" & myvalue), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Campus Data").Sort
.SetRange Range("E1:FA74")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlSortRows
.Apply
End With

End Sub
___________________________________________________________________________ ___________________________________________________________
Now I want to be able to make the range based on the size of the data set by using the column header name. The thing is it will Always start with column E, it is the end Column that can change based on size of data. The code below the first one is what I am trying to us and I get "Application-defined or object-defined error"

Trying to make the last column dynamic based on finding the column header which will simply be "1" and offset by one to set the end column.

Private Sub SortbyRow_Click()


Dim myRng As Range
'
'Use an Input box to select the row to sort
myvalue = InputBox("Row Number")

' Use to set range
Set myRng = Range("E").Find("1").Offset(1)


Columns("myRng").Select
ActiveWorkbook.Worksheets("Campus Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Campus Data").Sort.SortFields.Add Key:=Range( _
"E" & myvalue & ":FA" & myvalue), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Campus Data").Sort
.SetRange Range("E1:FA74")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlSortRows
.Apply
End With

End Sub