VBA that will change depending on row count

[gmooney]

Hi,

I have some code that essentially will do a re-sort of some data. Currently the data is not a table. One month the data might have 5,697 rows and the next month something like 3,154 rows.

I want to be able to have my VBA code always recognize the last row in the data that I want sorted.

Can someone give me an example of how to do this?

Here is some of my current code:

Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Items").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Items").Sort.SortFields.Add2 Key:=Range("F2:F5697" _
), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Items").Sort
.SetRange Range("A1:F5697")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

 

[Rick Rothstein]

Without seeing your worksheet, I am thinking something like this might work. Get rid of all the code about the With statement and then use this...

With ActiveWorkbook.Worksheets("Items").Sort
.SetRange .Range("A1").CurrentRegion
etc.

 

[gmooney]

Without seeing your worksheet, I am thinking something like this might work. Get rid of all the code about the With statement and then use this...

With ActiveWorkbook.Worksheets("Items").Sort
.SetRange .Range("A1").CurrentRegion
etc.

HI Richard,

Thank you for responding. What about the fact that I still reference to row 5697 in the code above the With statement?

 

[Micron]

Won't CurrentRegion stop at blank columns and rows? I was thinking to get the last row and last column with data?
Lcol = .Cells.Find("*", searchorder:=xlByColumns, SearchDirection:=xlPrevious).Column

Lrow = Cells.Find(what:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
searchorder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

 

[Skyybot]

This may work for you. I edited the code you provided slightly. Test on a copy of your Workbook as unexpected results may occur. I commented out the Select methods because I didn't see them being used.

'Range("A2").Select
'Range(Selection, Selection.End(xlToRight)).Select
'Range(Selection, Selection.End(xlDown)).Select
Dim lstRow as Long, lstCol as Long
lstRow = ActiveWorkbook.Worksheets("Items").UsedRange.Rows.Count
lstCol = ActiveWorkbook.Worksheets("Items").UsedRange.Columns.Count
ActiveWorkbook.Worksheets("Items").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Items").Sort.SortFields.Add2 Key:=Range("F2:F" & lstRow) _
), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Items").Sort
.SetRange Range("A1:F" & lstRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

 

[Rick Rothstein]

HI Richard,

Thank you for responding. What about the fact that I still reference to row 5697 in the code above the With statement?

I said to remove (get rid of) all the code above that With statement... as long as there are no blank rows in your data, CurrentRegion will reference all the cell in your data.