Suggestiong coding...

  • Thread starter Thread starter Newbie
  • Start date Start date
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
 
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
 
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)
 
Back
Top