Ranges

  • Thread starter Thread starter jimfkennedy
  • Start date Start date
J

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.
 
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)
 
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
 
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
 
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)
 
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
 
Back
Top