Offset one column

[Pam in TX]

I know this is a simple fix, but my brain just isn't getting it.

I want the macro to start on row 3, column E, look across the row and when it sees "1" offset back to the left one column, then select everything between. Right now we are having to adjust everything based on the columns between. So some files could be E:O while others might be E:X.

Here is what I have been using, but have to manually change it based on the columns needed.


Sub Button1_Click()
Dim myvalue As Variant
'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)


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


End Sub

Thank you in advance for the assistance

[SamT]

Pam,

I look at your code and I look at your text and I wonder what you have and what you are trying to to.






I see that you are sorting E:O on E or E:X on E, but what difference does it make which Row of E you assign as Key1?

Why don't you ever sort A:D and why Sometimes not Sort N:X. That has to break your data Records

Why are you looking for a 1 in a specific Row of column P and/or Y? What if it isn't anywhere in that Row?

[mjmartin_nz]

It does depend what the file structure is like but as long as the data is a block and does not have rows or columns surrounding it you could use the following:

set myRng = Range("E3").currentregion
set myRng = myRng.resize(myRng.rows.count, myRng.columns.count-1)

This does rely on the surrounding columns being clear otherwise the current region will be larger than you want. If you want to use the find method, and select all rows then you would need to use the following:

set myRng = sheet1.Rows(3).find("1", Lookat:=xlWhole) ' This finds the first instance of 1 in the 3rd row of the sheet
if not myRng is nothing then set myRng = myRng.offset(0, myrng.column - range("E").column).resize(1,range("E").column - myRng.column -1)

The bottom line offsets the found range back to the 'E' column, you made need to offset by +/- 1, and then increase the size of the range to include the found cell (once again +/- 1). - This will only select the 3rd row between column E and the 1, you will then need to resize to grab all of the columns.

Hope this helps.

[Pam in TX]

Thank you for the responses and questions. Let me see if I can make it a little clearer.

There is some data to the left and right, but it isn't necessary to include in the sort. Row 3 is actually my column header row. My crucial data is contained between column E and the column to the left of where the "1" appears. I need the macro to start on row 3, column E, look across the row and when it sees "1" offset back to the left one column, then select everything between across and then down to the last row of data on those columns only. Then sort based on the row the user selects.

I am still learning about all this, so I hope that helps. Thank you in advance for your help.

[SamT]

Do you understand that doing so can make the all the data records below and including the "1" unusable and unrecoverable?

[Pam in TX]

Do you understand that doing so can make the all the data records below and including the "1" unusable and unrecoverable?

Yes, in normal cases you are correct. In this case it will be just fine, since we are only looking at sorting across the row.

[SamT]

then select everything between across and then down to the last row of data on those columns only. Then sort based on the row the user selects. I don't understand the latter sentence.


Sub SubRangeSort()
'Finds 1 in Row3, sorts from E3 to 1 less then Column with 1
Dim LstSrtCol As Long
Dim LR As Long

LR = Range("E3").CurrentRegion.SpecialCells(xlCellTypeLastCell).Row
LstSrtCol = Rows(3).Find(What:=1, After:=Range("E3")).Offset(-1).Column

Range(Range("E3"), Cells(LR, LstSrtCol)).Sort 'Edit Sort parameters as needed
End Sub

[Pam in TX]

Thanks for the responses, still working on the solution.

[SamT]

I'm still working on the Question.