How does excel determine the range of a list?

[Rick Stambach]

Hey,
I'm working with worksheets that have more than one list on them. I need
to define the range of the list and I only know one cell of the list. How
does excel determine the range of a list if you select one cell and set the
autofilter or sort data? I'm doing this in VBA and was trying to find
something like FilterDatabase to define the range but these list are not
filtered or named.

 

[Per Jessen]

Hi

If first cell is A1 and your list your list has no empty rows in the column
before end of the list, you can use this:

Set FilterRange=range("A1",Range("A1").End(xlDown))

However if you have only one list in column A with empty rows in the list
starting in A1 you can use this:

Set FilterRange=range("A1",Range("A" & Rows.Count).End(xlUp))

HTH
Per

 

[Doug Glancy]

Rick,

I'm not sure if this helps, but here's a routine that looks at each list,
and if the selected cell is in it, prints the list name and range. I
believe you can filter on the range of the whole list:

Sub test()

Dim cell As Range
Dim lstList As ListObject

Set cell = Selection
For Each lstList In Me.ListObjects
If Not Intersect(cell, lstList.Range) Is Nothing Then
With lstList
Debug.Print "list range is: "; .Range.Address; "list name is: ";
..Name
'.Range.AutoFilter ... your filter code here
End With
End If
Next lstList

End Sub

hth,

Doug

 

[Mike Middleton]

Rick -

One way is:

Edit | Go To | Special | Current Region | OK

In a recorded macro:

Selection.CurrentRegion.Select

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[ShaneDevenshire]

Hi,

Excel does it by selecting the current region, doing the command, and the
collapsing back the the active cell. If you watch the screen during one of
these commands you will see a flash as Excel highlights the current region.

You can record this command by putting your cursor in a cell and pressing
either:
Ctrl+A
Shift+Ctrl+* (Ctrl plust the astricks)

Interesting to note - although both commands do the same thing, they do not
record the same code. One records an absolute cell address the other the
current region command:

Selection.CurrentRegion.Select
Range("A1:B101").Select

There is a second difference - the active cell remains fixed in one case and
moves to the top left corner of the selection in the other case.