Suggestiong coding...

N

Newbie

Is it better to use a RANGE or use the worksheet directly?
I will be using a userform for data entry and am building a navigational bar
I am THINKING using RANGE would be easier and more versitale. I will be
using different filters and I "THINK" that using RANGE is a little better in
this case.
I am correct or am I wrong? Why?

How do I create a RANGE of all records? How do I create a range for all
VISIBLE records? Is it different? (trying to find the answer myself, but
have not come across it yet...)



Thanks
XL Newbie
 
B

bruce taylor

This will find the last cell (visible or not). You could
test further after this routine whether the cell is
visible.

Sub LastNonBlankCell()
'Finds the last cell in a worksheet with an entry
(discounts formats & borders).
'Routine is very fast because it does NOT select/activate
each column or row.
Dim CurrCol As Long, CurrRow As Long
Dim Add As String

'This finds where Excel thinks the last cell is.
Add = Selection.SpecialCells(xlCellTypeLastCell).Address
'It either really is the last cell or the last cell is
inside it.
CurrCol = Range(Add).Column
CurrRow = Range(Add).Row

'TEST THE COLUMNS
'Test to see if this column is blank
CurrCol = CurrCol + 1 'ie the column one to the right
Do 'test in turn the columns to the left
CurrCol = CurrCol - 1
Loop Until Application.WorksheetFunction.counta(Columns
(CurrCol)) > 0
'CurrCol is now the column with the last data in it.

'TEST THE ROWS
'now test to see if this row is blank
CurrRow = CurrRow + 1 'ie one row lower
Do 'test in turn the rows above
CurrRow = CurrRow - 1
Loop Until Application.WorksheetFunction.counta(Rows
(CurrRow)) > 0
'CurrRow is now the row with the last data in it.

'position the 'last' cell
Cells(CurrRow, CurrCol).Activate

End Sub
 
B

Bob Phillips

You don't get a choice. If you use the Range object, that is part of a
Worksheet object. If you don't specify which object, it defaults to the
active worksheet.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top