Allowing for variable lines in macros

[smurrock]

I am trying to set up a macro to run for a data file that will not have a
consistent number of lines each month. This seems like it should be simple,
however I am struggling. I tried using relative references but either I
didn't set it up right or it won't work. I also tried using offset which
also failed. This seems pretty basic??

Thanks!!
Sidonia

[Don Guillett]

You didn't post your code so see if this idea helps to find the last row in
col A

lastrow=cells(rows.count,"a").end(xlup).row

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"smurrock" <smurrock@discussions.microsoft.com> wrote in message
news:3E5E4733-D3F0-4A0F-88A6-C687298083D5@microsoft.com...
>I am trying to set up a macro to run for a data file that will not have a
> consistent number of lines each month. This seems like it should be
> simple,
> however I am struggling. I tried using relative references but either I
> didn't set it up right or it won't work. I also tried using offset which
> also failed. This seems pretty basic??
>
> Thanks!!
> Sidonia

[Tom Ogilvy]

Basic approach:

Dim rng as Range, cell as Range
With ActiveWorkbook.worksheets(1)
set rng = .range(.cells(1,1),.cells(rows.count,1).End(xlup))
End With

for each cell in rng


Next

--
Regards,
Tom Ogilvy


"smurrock" wrote:

> I am trying to set up a macro to run for a data file that will not have a
> consistent number of lines each month. This seems like it should be simple,
> however I am struggling. I tried using relative references but either I
> didn't set it up right or it won't work. I also tried using offset which
> also failed. This seems pretty basic??
>
> Thanks!!
> Sidonia

[Gary''s Student]

Try using ActiveSheet.UsedRange. It should reflect the rows in your current
data:

Set r = ActiveSheet.UsedRange

nLastRow = r.Rows.Count + r.Row - 1
MsgBox ("last row " & nLastRow)

nLastColumn = r.Columns.Count + r.Column - 1
MsgBox ("last column " & nLastColumn)

nFirstRow = r.Row
MsgBox ("first row " & nFirstRow)

nFirstColumn = r.Column
MsgBox ("first column " & nFirstColumn)

numrow = r.Rows.Count
MsgBox ("number of rows " & numrow)

numcol = r.Columns.Count
MsgBox ("number of columns " & numcol)


etc.
--
Gary''s Student


"smurrock" wrote:

> I am trying to set up a macro to run for a data file that will not have a
> consistent number of lines each month. This seems like it should be simple,
> however I am struggling. I tried using relative references but either I
> didn't set it up right or it won't work. I also tried using offset which
> also failed. This seems pretty basic??
>
> Thanks!!
> Sidonia