Ranges

[jimfkennedy]

Hi,

I have some records on a spreadsheet, I want to select them with a
macro the range is A1 to m1 down to however many records there happen
to be? how do I specify this for my excel macro?

Thanks,

Jim.

 

[Bob Phillips]

Jim try this to get the end

Dim iLastRow As Long, liLastCol As Long

On Error Resume Next
iLastRow = 1: iLastCol = 1
With ActiveSheet.Range("A:M")
iLastRow = .Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
iLastCol = .Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByColumns, xlPrevious).Column
.Cells(iLastRow, iLastCol).Select
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Roger Govier]

Hi Jim
One way,
Insert>Name>Define enter name for range (e.g. Data ) in the Names in
Worbook pane
In the Refers to pane enter
=OFFSET($A$1,0,0,COUNTIF($A$1:$A$10000,"<><"),13)
then just refer to the range Data thereafter
Change ranges to suit

 

[jimfkennedy]

Hi Bob,

Thats good, but sometimes there may not be any data in the last column,
but I need it for the tabs and carriage returns (that will be used for
processing later on). Basically I need a1-m1 downwards??

Any ideas??

Jim

 

[Bob Phillips]

That does that Jim, it will return the lastrow, and you can substitute 13
for the last column.

Set rng = .Range(.Range("A1"),.Cells(iLastRow, 13)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jerry W. Lewis]

ActiveSheet.UsedRange.Rows.Count

will give you the number of the last row that contains data. If it is
possible that one or more of the first rows may contain nothing, then use

ActiveSheet.UsedRange.Rows.Count + (ActiveSheet.UsedRange.Row - 1)

You could simplify to

ActivSheet.UsedRange.Select

If there are no columns that you want to exclude.

Jerry