dynamic range -1 row

[GA3]

I am trying to dynamically select the range on a worksheet based on the cell not being blank but the last row of my data is a totals row. I can figure out how to get my data but I can not figure out how to reduce the selection by 1 row so that the totals row is not included.

My data starts in A4 and in the example I am using, the last cell with data is J128, but row 128 is the totals row. This will change based on the week/month and worksheet I run this on, so it needs to be able to find the end by itself.

Either...

Range("A4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Or...

Range(("A4"), Range("j65536").End(x1Up)).Select

seem to get me the selection of data that includes the total row. How do I get it to move the selection 1 row higher?

thanks!

 

[Zack Barresse]

Hi there,

There are a lot of ways to do this, here are a couple of examples...

    Dim rTest As Range
    Set rTest = Range("A4", Cells(Rows.Count, "J").End(xlUp).Offset(-1, 0))


    Dim lLastRow As Long
    lLastRow = Cells(Rows.Count, "J").End(xlUp).Row - 1
    Set rTest = Range("A4:J" & lLastRow)

HTH

 

[GA3]

Thanks Zack, I am having trouble getting anything to work. I have replaced my macro with each of the examples you sent (1 at a time) but nothing highlights for selection. What am I doing wrong?

 

[Zack Barresse]

Nothing, my code doesn't select (aka highlight) anything. That isn't good coding practice. It instead sets it as a range variable from which you can work with, much more efficient. Can you explain what it is you are trying to accomplish? You can work with the range variable just like you would with the Selection if you would have selected it.

 

[GA3]

I have sales data that I need to sort from lowest to highest. This is part of a large macro that spans 20+ sheets. I have to go into 10 of those sheets due to them being different customer groups to do this, and all of them have a different number of rows that can change week to week, month to month. They are never constant.

It would just save time to be able to include in my macro the ability for the sheet to be sorted by the sales data, but I must select the range in order to sort the data.

With there being different #'s of rows, I need to (as part of my macro) select the range on each sheet. Each range, if just selected by itsself, will still contain the totals row which I need to remain at the bottom of my sort. Therefore, I can not just select based on the information in my 1st post. I need the selection to reduce itself by 1 row to exclude the total row.

I hope this explains it better. Thank you and sorry for any confusion.

 

[Zack Barresse]

I have sales data that I need to sort from lowest to highest. This is part of a large macro that spans 20+ sheets. I have to go into 10 of those sheets due to them being different customer groups to do this, and all of them have a different number of rows that can change week to week, month to month. They are never constant.

Great explanation. It gives a much better picture. Thanks.

... but I must select the range in order to sort the data.

No you don't.


So you want to perform this action only on certain sheets? Are there names for those sheets you can give us? Why not just have all of these sheets done once? You only need to specify what sheets you want this run on, no selecting needed. Identify the sheet names for us, as well as what row your header data is on. I'm assuming row 3 since you said your data starts on row 4 of A. And just to make sure, you want to test the last cell in the range based on column J? Will the 'totals' row always have a value in that column? Or do you want to judge by another column (i.e. column A) and have the range extend to column J?

 

[GA3]

Well for simplification & privacy lets just call them sheets "A" through "N" (there were actually 14 not 10).

The header data is in Row 4 (I was including the header row because I could identify it as such in the sorting function, thus excluding it).

The data spans from column A through Column J. J will always contain data that pertains to the rest of the cells because it is a percentage of other values calculated in the row.

The last row will always be the totals row.

Is this enough information?

 

[Zack Barresse]

You don't say what the key will be. I'm assuming it's column A, so the key would be A4. You should be able to see it in the code, just change it as necessary. I would also change the routine name to something meaningful to you. And you did say ascending, right?

Sub SortMySheetsDataRanges()
    Dim WS As Worksheet
    For Each WS In ThisWorkbook.Worksheets
        Select Case WS.Name
        Case "A", "B", "C", "Etc"
            With WS.Range("A4", WS.Cells(WS.Rows.Count, "J").End(xlUp).Offset(-1, 0))
                .Sort Key1:=WS.Range("A4"), Order1:=xlAscending, Header:=xlYes
            End With
        End Select
    Next WS
End Sub

Also look at the names in the Select statement. Change as needed for your application. It assumes you'll copy this into a standard module in the workbook which you are going to run it (hence the ThisWorkbook reference). If the names of the sheets will change, we'll need to find another way to run this.

HTH

 

[GA3]

Zack,

I made my alterations to the tab names and the key row but it works perfectly! Thank you so much!