xlDown not returning last used cell

[Old Bloke]

Hi. I need to be able to select chunks of data (for sorting) approx. 120 rows (it varies +/- 25) by 4 columns. The data is in various location on a fairly long spreadsheet. I need to be able to select a range of data from one cell below an active cell to the last used cell in same column (there is always a blank cell to create the last used criteria) and extend the selection to the adjoining three columns. The code I have written works - only to the extent that it selects the 4 columns but only goes down 31 rows - no matter where I start, 31 rows!

It may be something to do with my use of "xlDown" as a variable in the Offset function.

Any help will be most appreciated (I have spent hours on it)

This my code :-

Range(ActiveCell.Offset(1,0), ActiveCell.Offset ((ActiveCell.End(xlDown)).Select

Thanks

[Old Bloke]

Sorry, typo in my post. Code is

Range(ActiveCell.Offset(1,0) , ActiveCell.Offset((ActiveCell.End(xlDown), 3)).select

[Sandler]

Can you send over the workbook with the code to look at?

[jolivanes]

This should be easy to understand and also easy do adapt.

Sub Get_My_Range()
Dim lr As Long, cc As Long, cr As Long
cc = ActiveCell.Column
cr = ActiveCell.Row
lr = Cells(Rows.Count, cc).End(xlUp).Row
Range(Cells(cr, cc), Cells(lr, cc + 3)).Select
End Sub
If you want the above in a one liner, it'll be something like this.

Sub Get_My_Range_A()
Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row, ActiveCell.Column + 3)).Select
End Sub

[Old Bloke]

Hi Sandler

Thanks for your interest.

The data I am handling is sensitive so I have made up a workbook that shows basically what I am trying to do. I have included my code and two suggested my Jolivanes - unfortunately have not made any progress.
Still don't understand why my code does not work.

How do I send you the workbook - ! cannot locate an attachment function

[jolivanes]

Click on "Go Advanced" at the bottom RH side and then click on "Manage Attachments".

[Old Bloke]

21253

[Old Bloke]

Sorry the macros in the last workbook are missing (not sure why) - try this one

[Paul_Hossler]

I'd do it this way

In the data ws code module have a double-click event that works if one of the 2 'Sort by' column headers are double clicked




Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.EnableEvents = False
If Target.Cells(1, 1).Value = "Sort by date" Then
Call SortData(Target.Cells(1, 1), 1)
ElseIf Target.Cells(1, 1).Value = "Sort Alphabetically" Then
Call SortData(Target.Cells(1, 1), 2)
End If
Application.EnableEvents = True
End Sub




In a standard module, just a simple sort

this remove col A from the sort, but if the block is always by month, there should be no need



Option Explicit

Sub SortData(RangeToSort As Range, ColumnToSort As Long)
Dim rSort As Range, rSort1 As Range

Application.ScreenUpdating = False

Set rSort = RangeToSort.CurrentRegion
Set rSort = rSort.Cells(1, 2).Resize(rSort.Rows.Count, rSort.Columns.Count - 1) ' get rid of A
Set rSort1 = rSort.Cells(2, 1).Resize(rSort.Rows.Count - 1, rSort.Columns.Count) ' get rid of row 1 - headers

With rSort.Parent.Sort
.SortFields.Clear
.SortFields.Add Key:=rSort1.Columns(ColumnToSort), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange rSort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.ScreenUpdating = True
End Sub

[Old Bloke]

Paul
Love your double click activation/check that the correct header is selected - will incorporate that.
However, I still have my original issue of being able to select the data I want to sort.

[Paul_Hossler]

Paul
Love your double click activation/check that the correct header is selected - will incorporate that.
However, I still have my original issue of being able to select the data I want to sort.

1. Thanks

2. Each block (i.e. Month) is sorted by date or company by double clicking one of the two header cells ("Sort by ...") for that block

How did you want it to sort and what do you want it to sort?

[Old Bloke]

Thanks again for your interest Paul.

Sorting is not my problem but the selection of the data to be sorted.

My code - Range(ActiveCell.Offset(1,0) , ActiveCell.Offset((ActiveCell.End(xlDown), 3)).Select - selects the required four columns wide but only extends down 31 rows rather than the 110 (+/-) before a blank row. The blank row is there to establish the "last used" criteria.
It does not matter in what row I activate a cell - 31 rows, 4 columns wide, is all I get.

[jolivanes]

Do you also get only 31 Rows with the code in Post #4?

[Old Bloke]

Hi Jolivanes. No - they both select four columns wide but every row in the workbook. I think it may be (xlUp) that is doing that - my understanding is that xlUp goes to the very last row of the workbook and then works up until it finds a filled cell/row. I tried changing it to (xlDown) but it did the same thing. Thanks

[Aussiebear]

Why not just name your sections (eg. A3:E59) as January? Then deal with each range as you see fit.

[Paul_Hossler]

Sorting is not my problem but the selection of the data to be sorted.

My code - Range(ActiveCell.Offset(1,0) , ActiveCell.Offset((ActiveCell.End(xlDown), 3)).Select - selects the required four columns wide but only extends down 31 rows rather than the 110 (+/-) before a blank row. The blank row is there to establish the "last used" criteria.
It does not matter in what row I activate a cell - 31 rows, 4 columns wide, is all I get.


Really not understanding

This picture is from your attachment (with some interior block rows hidden)

I see lots of blank rows separating monthly blocks as well as the blank rows from April down

Using my little sub below, I select four columns from the active cell down to the last row with data, and I never get just 31 rows

What is it you're really trying to do, not necessarily how you want to do it?

I thought your finally objective was to sort each monthly block by day or company. My suggested earlier sub that used a double click event did that without having to select any thing since it used .CurrentRegion

If you're final objective is to sort all the monthly blocks by date / company WITHIN each block, that's very different


Maybe a very specific example would help: what cell you're activating, what you'd expected, why you're doing it that way, etc.


21260



Option Explicit

Sub NotUnderstanding()
Range("B3").Select
Range(ActiveCell, ActiveCell.End(xlDown).Offset(, 4)).Select
MsgBox Selection.Address ' B3:F59

Range("B23").Select
Range(ActiveCell, ActiveCell.End(xlDown).Offset(, 4)).Select
MsgBox Selection.Address ' B23:F59

Range("B43").Select
Range(ActiveCell, ActiveCell.End(xlDown).Offset(, 4)).Select
MsgBox Selection.Address ' B43:F59

Range("B67").Select
Range(ActiveCell, ActiveCell.End(xlDown).Offset(, 4)).Select
MsgBox Selection.Address ' B67:F118
Range("B126").Select
Range(ActiveCell, ActiveCell.End(xlDown).Offset(, 4)).Select
MsgBox Selection.Address ' B126:F177
End Sub

[jolivanes]

Re your Post #14
Which Cell is the ActiveCell, = Selected Cell, when you run that code?

[Old Bloke]

Hi Paul

This is exactly what I was after - your new code works where my code failed.

I still don't understand why mine didn't work, but that's academic now (forget and move on).

Many thanks to you (and the others) for taking time to help me.

I may be back if I need help with the BeforeDoubleClick routine

[Paul_Hossler]

OK

BTW, it's not necessary to Select the cells in order to sort a range, especially when the data is 'blocked' and you can use .CurrentRange