Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allowing for variable lines in macros
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allowing for variable lines in macros
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 "smurrock" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allowing for variable lines in macros
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allowing for variable lines in macros
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
30-yr Amortization allowing 26 payments per year/780 lines vs 360 | Excel Worksheet Functions | |||
How can I lock worksheets while still allowing macros to operate? | Excel Discussion (Misc queries) | |||
How can I lock worksheets while still allowing macros to operate? | Excel Discussion (Misc queries) | |||
Allowing for variable cell reference in macro | Excel Worksheet Functions | |||
Allowing Macros in Excel | Setting up and Configuration of Excel |