how to write a macro, to refer to a last row in a file, in which .

R

raji

I have the follwing table
week B C D E F
G H
434 674 453 34 0 13 0 0
435 669 298 29 2 50 3 30
436 673 609 32 8 22 3 90
437 672 872 41 21 2 7 83
438 672 946 54 7 1 5 76
439 660 545 62 19 2 4 74
I need a macro to refer to the last row...that is 6th in this case above.
But that will change to the 7th row..or 8th etc. Always in this file I need
to refer to the last row.
Can we do it in a macro?
-raji
 
B

Bernie Deitrick

Raji,

There are a number of ways of doing that, the simplest is

Dim myLastRow As Range
Dim myCell As Range

Set myCell = Range("B65536").End(xlUp)
Set myLastRow = Intersect(myCell.CurrentRegion, myCell.EntireRow)
MsgBox "The last row cells are " & myLastRow.Address
MsgBox "The last row number is " & myLastRow.Row

Then you can do whatever you need.

HTH,
Bernie
MS Excel MVP
 
G

Guest

hi,
Sub maclastrow()
Dim rng As Range
Dim lastrow As Integer
Set rng = Range(Cells(1, "B"), Cells(Rows.Count, "B").End
(xlUp))
' and if you need the row number
lastrow = rng.Rows(rng.Rows.Count).Row
MsgBox "Last row is " & lastrow

End Sub
 
R

raji

Bernie and anonymous,
I will try to embed it in my script. Since I'am not a VBscript expert, can
you tell
if there is a function or a key that I could use to goto last row in a file.
It willbe easier if I can record a macro. (My VBscript knowledge is limited
to this way.. record macro and see the scipt in the debugger).
-raji
 
B

Bernie Deitrick

Alan,
the above will not work if B65536 is not empty
Click to expand...

"The above" won't work if column B is filled from the bottom up, but it will
work if _only_ B65536 is filled (along with a table at the top). Not that
I've ever seen anybody fill in columns from the very bottom, or even have
something in the last row. <vbg> And since the OP talked about rows 6, 7,
or 8.....

Bernie
 
B

Bernie Deitrick

Raji,

If you are _certain_ that you will always have more than one row, simply
start the recorder, select the top cell of column B, then click the
"Relative Reference" button on the "Stop recording" toolbar, and then use
End-down arrow. The code that you'll get will then work independent of your
table size.

You'll get code like

Range("B2").Select
Selection.End(xlDown).Select

If you want to do something with the blank cell beneath, simply hit the down
arrow one more time, and you'll record this additional line:

ActiveCell.Offset(1, 0).Range("A1").Select

HTH,
Bernie
MS Excel MVP
 
R

raji

Thats more like it. Thanks for your help.
-raji

Bernie Deitrick said:
Raji,

If you are _certain_ that you will always have more than one row, simply
start the recorder, select the top cell of column B, then click the
"Relative Reference" button on the "Stop recording" toolbar, and then use
End-down arrow. The code that you'll get will then work independent of your
table size.

You'll get code like

Range("B2").Select
Selection.End(xlDown).Select

If you want to do something with the blank cell beneath, simply hit the down
arrow one more time, and you'll record this additional line:

ActiveCell.Offset(1, 0).Range("A1").Select

HTH,
Bernie
MS Excel MVP
Click to expand...
 
R

raji

Bernie,
I just tried it out and seems like that is not what I want. I may be using
it wrong.
This is what I need to do.
open the sheet.
Copy and pate the first row somewhere in the sheet
then Copy and paste the last row ( which may be variable every time) below it
Name this row as WTD.
Then use math func, to calculate the average of first row : last row ( which
I said is variable everytime). Copy this data below the WTD row. Call it QTD.
( I will be using these 2 rows to graph later on)

B C D E F
434 674 453 34 0 13 0 0
435 669 298 29 2 50 3 30
436 673 609 32 8 22 3 90
437 672 872 41 21 2 7 83
438 672 946 54 7 1 5 76
439 660 545 62 19 2 4 74

Output table is which I will use to graph.

C D E F
WTD 660 545 62 19 2 4...
QTD =average ( c2:last row) =average (d1:last row) .....
Can you help?


-thanks
raji
 
B

Bernie Deitrick

raji,

Select a single cell in your data block prior to running the macro below.
This assumes there is nothing else on your sheet except the table.

If you can't get this to work, contact my privately and I will send you a
working version.

HTH,
Bernie
MS Excel MVP

Sub Raji()
Dim myRange As Range
Dim myRow As Long
Dim myCol As Integer

Set myRange = ActiveCell.CurrentRegion
myRow = myRange.Rows.Count + myRange(1).Row + 2

myCol = Application.Max(1, myRange(1).Column - 1)

myRange(1).EntireRow.Copy _
Cells(myRow, 1).EntireRow
myRange(myRange.Cells.Count).EntireRow.Copy _
Cells(myRow + 1, 1).EntireRow
Cells(myRow + 1, myCol).Value = "WTD"
Cells(myRow + 2, myCol).Value = "QTD"

Intersect(Rows(myRow + 1 & ":" & myRow + 2), _
myRange.Columns.EntireColumn). _
SpecialCells(xlCellTypeBlanks).Formula = _
"=AVERAGE(" & myRange.Columns(1).Cells.Offset(1, 0). _
Resize(myRange.Rows.Count - 1, 1).Address(False, False) & ")"
End Sub
 
R

raji

Bernie,
It works perfectly.
I just had only 1 question..the new table created after this macro works is
getting pasted in 2 rows below the original table. I'am wondering what will
happen if my original table data grows..as it will always.
Will this new table created be automatically pushed out or should I select
some spare place somewhere else in the sheet.

Thanks once again.. your comments are very very helpful.
-raji
 
B

Bernie Deitrick

Raji,

The macro can deal with any size original data block, and will always put
the resulting table two rows beneath it. So, you're good for any future
size changes.

HTH,
Bernie
MS Excel MVP
 
R

raji

Bernie,
Thanks for your help.
-raji

Bernie Deitrick said:
Raji,

The macro can deal with any size original data block, and will always put
the resulting table two rows beneath it. So, you're good for any future
size changes.

HTH,
Bernie
MS Excel MVP
Click to expand...
 
Top