Help finding the end of a worksheet

[Dan Wilson]

Good day. I am running Excel out of Office365 (updated) on Windows 10 Home. I have a worksheet that contains 2980 Rows with Columns A through H containing data. Periodically, I add more records to the worksheet. There are 6 Macros that I use to sort the data. There are currently formulas in the Macros that search from Row 2 through Row 2980. Each time that I add more records to the worksheet, it is necessary to edit the Macros to alter the search formulas to search ALL of the Rows.
Here is an example of the search :
ActiveWorkbook.Worksheets("50-69").Sort.SortFields.Add2 Key:=Range("B2:B2980" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

Is there a way to identify the last used Row instead of editing the Range? Thank you for any help.
Dan Wilson...

 

[Legacy 143009]

Yes, you can get the last row in sheet 50-69 like this:

Dim lRow As Long
lRow = Worksheets("50-69").Cells(Rows.Count, 1).End(xlUp).Row

Then you can use it like this:

ActiveWorkbook.Worksheets("50-69").Sort.SortFields.Add2 Key:=Range("B2:B" & lRow _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

 

[HaHoBe]

Hi Dan Wilson,

maybe

With ActiveWorkbook.Worksheets("50-69")
  .Sort.SortFields.Add2 Key:=.Range("B2:B" & .Cells(.Rows.Count, "B").Row), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending, _
                        DataOption:=xlSortNormal
End With

Holger

 

[Dan Wilson]

Yes, you can get the last row in sheet 50-69 like this:

Dim lRow As Long
lRow = Worksheets("50-69").Cells(Rows.Count, 1).End(xlUp).Row

Then you can use it like this:

ActiveWorkbook.Worksheets("50-69").Sort.SortFields.Add2 Key:=Range("B2:B" & lRow _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

Good day Flashbond. Thank you for your help. I edited my Macro per your instructions and there is a problem. First, I could not identify whether the value
Dim 1Row As Long
was the number one or the letter l in front of the word Row. I tried both combinations and neither one worked. At this point it is probably best to give you the entire Macro incas there is something that I missed or used incorrectly. The following is the entire Macro in Italics.

Sub by_artist_t()
'
' by_artist Macro
' sort by artist, year, chart
' 1/24/2023

'

Dim lRow As Long
lRow = Worksheets("50-69").Cells(Rows.Count, 1).End(x1Up).Row
Cells.Select
ActiveWorkbook.Worksheets("50-69").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("50-69").Sort.SortFields.Add2 Key:=Range("B2:B" & lRow _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("50-69").Sort.SortFields.Add2 Key:=Range("E2:E" & lRow _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("50-69").Sort.SortFields.Add2 Key:=Range("D2:D" & lRow _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("50-69").Sort
.SetRange Range("A1:K" & lRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("I2").Select
End Sub

I added 5 records to the worksheet to test the Macro. When I run the Macro, I get a Run-time error '1004':. Debug then highlights the line below the Dim 1Row As Long. If I change the number 1 to the letter l, the results are the same. Please take a look and see if you can see what I have done wrong.
Thank you,
Dan Wilson...

 

[HaHoBe]

Hi Dan,

compiling the code you would need to alter x1Up to read xlUp as all Excel constants would start with XL (in small letters). And the small L before any variable should flag these Variables to be of Type Long.

'lRow = Worksheets("50-69").Cells(Rows.Count, 1).End(x1Up).Row ' command spelled with number one
lRow = Worksheets("50-69").Cells(Rows.Count, 1).End(xlUp).Row ' command spelled with small L

By hitting the top left corner of the codefield you have the possibility to copy the code to Clipboard ready to insert into the VBE. I would describe that symbol to resemble two sheets of paper.

Please use code-tags in the future to display your procedures here.

Holger

 

[Dan Wilson]

Hi Dan,

compiling the code you would need to alter x1Up to read xlUp as all Excel constants would start with XL (in small letters). And the small L before any variable should flag these Variables to be of Type Long.

'lRow = Worksheets("50-69").Cells(Rows.Count, 1).End(x1Up).Row ' command spelled with number one
lRow = Worksheets("50-69").Cells(Rows.Count, 1).End(xlUp).Row ' command spelled with small L

By hitting the top left corner of the codefield you have the possibility to copy the code to Clipboard ready to insert into the VBE. I would describe that symbol to resemble two sheets of paper.

Please use code-tags in the future to display your procedures here.

Holger

Good day HaHoBe and THANK YOU! After much editing of 6 different macros, all is now working as desired. I must apologize to you for not replying to you in the first place. Your response about the lower case "L" was something that I did not know, and I did not understand your first response. I'm sure it would work.