Another last cell detection problem

[NikkoW]

I have 6 columns of data. I need to find the bottom, right-most cell (I
don't know what row that is since it changes from worksheet to worksheet)
and then select the whole area from A4 to this last cell.

The problem is that there is no guarantee that all cells will have data in
them (so the x1end command will stop before reaching my desired cell). Well,
to be precise, columns A, B and C are all populated but D, E and F only have
intermittent entries.

Once I have that range, I need to use it in a

Range(MyRange).AdvancedFilter Action:=xlFilterInPlace, Unique:=True

Instruction so I need to assign this changing area to a variable, MyRange.

Thanks in advance

 

[Jeff Standen]

As long as column A is fully populated this will work fine for the selection
of the range.

Range("A4:F4", Range("A4:F4").End(xlDown)).Select

Cheers,

Jeff

 

[Bob Flanagan]

Nikko, another approach in case you can't guarantee which column is the
longest or if it has no blanks:

dim R as long, N as long
for I = 1 to 6
R = cells(65536,i).end(xlup)
if R > N then N = R
next
Range(range("a4"), cells(N,6)).select

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[JMay]

In a regular Module:

Sub GetLastRealCell()
Dim RealLastRow As Long
Dim RealLastColumn As Long
Range("A1").Select
On Error Resume Next
RealLastRow = _
Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
MsgBox "The Last Populated Cell in this Worksheet is " &
Cells(RealLastRow, RealLastColumn).Address
End Sub