Need syntax help in Macro to identify last row of Excel data 1

[kendue]

I'm using Office 97 version of Excel in a daily invoice report which has a variable number of line-item records each day. This Excel file is linked to Access which does the summarizing and formatting chores for the report.

Each line-item record has the invoice total repeated for that invoice, and I have a formula in the last column that shows the total only once for each invoice number for proper totaling in Access.

I've set up a Macro that copies the formula down the column, but I need help with the proper syntax within the macro to stop the copying at the last row instead of copying for a set number of rows.

Any help will be greatly appreciated.

 

[SkipVought]

hi,

dim lFirstRow as long, lLastRow as long

with activesheet.usedrange
   lfirstrow = .row
   llastrow = .row + .rows.count - 1
end with

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kendue]

Skip,

Since I am a novice at working with code within a macro, I still don't know how to tie your code into my macro.

What I want to accomplish in code is the same process that can be performed manually on my spreadsheet by typing a formula into cell W2 and then double-clicking on that cell's handle. Is there a code instruction{s} that will perform this same function along the lines of "autofill"?

 

[SkipVought]

Your question asked to "identify last row of Excel data"

You never posted any code. I have no idea WHAT you intend to copy from WHERE and no understanding of the structure of the data on your sheet.

So I answered your basic question.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kendue]

As I explained in my follow-up message, I want to copy the formula placed in the cell "W2" down that column for as many rows as there are lines of data which will vary from day to day.

If done manually, this can be accomplished by simply double-clicking on cell "W2"'s handle. I don't see what difference the structure of the data or the make-up of the formula being copied makes. The process is what matters.

Instead of ending up with a defined range to autofill of, say "W2:W200" which is what happens when I record a macro, I want the macro to handle a varying column length of "W2:whatever".

I hope this clarifies my problem. Again, any help will be appreciated.

 

[SkipVought]

Did you not record a macro?

Please post your code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kendue]

Sub copyformula()
'
' copyformula Macro
' Macro recorded 6/3/2011
'

'
ActiveCell.FormulaR1C1 = "=IF(RC[-20]<>R[-1]C[-20],RC[-1],0)"
Selection.AutoFill Destination:=Range("W2:W200")
Range("W2:W200").Select

End Sub

 

[SkipVought]

Sub copyformula()
'
' copyformula Macro
' Macro recorded 6/3/2011 
'

'
dim lFirstRow as long, lLastRow as long

with activesheet.usedrange
   lfirstrow = .row
   llastrow = .row + .rows.count - 1
end with

    ActiveCell.FormulaR1C1 = "=IF(RC[-20]<>R[-1]C[-20],RC[-1],0)"
    Selection.AutoFill Destination:=Range(Cells(2,"W"), Cells(llastrow,"W"))
    
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kendue]

Skip,

That is precisely what I needed.

Thank you so much for your help!