How can I format the last row !
 

I am using Excel2002sp3 on WinXP. I have written

Worksheets("Report(metArb)").Activate
Range("B3").Select
Selection.End(xlDown).Select
lastfilledrow = ActiveCell.Row
ActiveSheet.Cells(lastfilledrow + 2, 5).Value = "Totaal"
sumcellformula = "=sum(h2:h" & lastfilledrow & ")"
ActiveSheet.Cells(lastfilledrow + 2, 8).Formula = sumcellformula
sumcellformula = "=sum(i2:i" & lastfilledrow & ")"
ActiveSheet.Cells(lastfilledrow + 2, 9).Formula = sumcellformula
sumcellformula = "=sum(j2:j" & lastfilledrow & ")"
End Sub

to find the last row and make some totals. This seems to work okay, but I am
having trouble selecting the lastfilledrow+2 ... that is selecting the whole
row. I'm sure it is simple, but I can't for the love of me figure out how to
do it. Basically .... how can I format the lastfilledrow+2, columns 5 to 9,
so that they are bold and red.... or basically how can I select the whole or
part of a row so I can format it ?

thanks for your help ... Roger

How can I format the last row !
 

Try this. This will Bold and turn the text Red for the row and columns you
are referring to.

With Range(Cells(lastfilledrow + 2, 5), Cells(lastfilledrow + 2, 9))
.Font.ColorIndex = 3
.Font.Bold = True
End With
--
Cheers,
Ryan


"Ro477" wrote:

I am using Excel2002sp3 on WinXP. I have written

Worksheets("Report(metArb)").Activate
Range("B3").Select
Selection.End(xlDown).Select
lastfilledrow = ActiveCell.Row
ActiveSheet.Cells(lastfilledrow + 2, 5).Value = "Totaal"
sumcellformula = "=sum(h2:h" & lastfilledrow & ")"
ActiveSheet.Cells(lastfilledrow + 2, 8).Formula = sumcellformula
sumcellformula = "=sum(i2:i" & lastfilledrow & ")"
ActiveSheet.Cells(lastfilledrow + 2, 9).Formula = sumcellformula
sumcellformula = "=sum(j2:j" & lastfilledrow & ")"
End Sub

to find the last row and make some totals. This seems to work okay, but I am
having trouble selecting the lastfilledrow+2 ... that is selecting the whole
row. I'm sure it is simple, but I can't for the love of me figure out how to
do it. Basically .... how can I format the lastfilledrow+2, columns 5 to 9,
so that they are bold and red.... or basically how can I select the whole or
part of a row so I can format it ?

thanks for your help ... Roger

How can I format the last row !
 

with Worksheets("Report(metArb)")
lastfilledrow = .Range("B3").Row
.Cells(lastfilledrow + 2, 5).Value = "Total"
sumcellformula = "=sum(h2:h" & lastfilledrow & ")"
.Cells(lastfilledrow + 2, 8).Formula = sumcellformula
sumcellformula = "=sum(i2:i" & lastfilledrow & ")"
.Cells(lastfilledrow + 2, 9).Formula = sumcellformula
sumcellformula = "=sum(j2:j" & lastfilledrow & ")"
Set ColorRange = .Range("E" & LastRow & ":I" & LastRow)
end with

"Ro477" wrote:

I am using Excel2002sp3 on WinXP. I have written

Worksheets("Report(metArb)").Activate
Range("B3").Select
Selection.End(xlDown).Select
lastfilledrow = ActiveCell.Row
ActiveSheet.Cells(lastfilledrow + 2, 5).Value = "Totaal"
sumcellformula = "=sum(h2:h" & lastfilledrow & ")"
ActiveSheet.Cells(lastfilledrow + 2, 8).Formula = sumcellformula
sumcellformula = "=sum(i2:i" & lastfilledrow & ")"
ActiveSheet.Cells(lastfilledrow + 2, 9).Formula = sumcellformula
sumcellformula = "=sum(j2:j" & lastfilledrow & ")"
End Sub

to find the last row and make some totals. This seems to work okay, but I am
having trouble selecting the lastfilledrow+2 ... that is selecting the whole
row. I'm sure it is simple, but I can't for the love of me figure out how to
do it. Basically .... how can I format the lastfilledrow+2, columns 5 to 9,
so that they are bold and red.... or basically how can I select the whole or
part of a row so I can format it ?

thanks for your help ... Roger

How can I format the last row !
 

I took the liberty of cleaning up your code a bit. I would give this a try.
It should do the same thing. If not, let me know.

Option Explicit

Sub TEST()

Dim lngLastRow As Long

With Sheets("Report(metArb)")
lngLastRow = .Cells(Rows.Count, "B").End(xlDown).Row + 2
.Cells(lngLastRow, 5).Value = "Total"
.Cells(lngLastRow, 8).Formula = "=sum(H2:H" & lngLastRow & ")"
.Cells(lngLastRow, 9).Formula = "=sum(I2:I" & lngLastRow & ")"
End With

End Sub

Hope this helps! If so, let me know and click "YES" below.
--
Cheers,
Ryan


"Ro477" wrote:

I am using Excel2002sp3 on WinXP. I have written

Worksheets("Report(metArb)").Activate
Range("B3").Select
Selection.End(xlDown).Select
lastfilledrow = ActiveCell.Row
ActiveSheet.Cells(lastfilledrow + 2, 5).Value = "Totaal"
sumcellformula = "=sum(h2:h" & lastfilledrow & ")"
ActiveSheet.Cells(lastfilledrow + 2, 8).Formula = sumcellformula
sumcellformula = "=sum(i2:i" & lastfilledrow & ")"
ActiveSheet.Cells(lastfilledrow + 2, 9).Formula = sumcellformula
sumcellformula = "=sum(j2:j" & lastfilledrow & ")"
End Sub

to find the last row and make some totals. This seems to work okay, but I am
having trouble selecting the lastfilledrow+2 ... that is selecting the whole
row. I'm sure it is simple, but I can't for the love of me figure out how to
do it. Basically .... how can I format the lastfilledrow+2, columns 5 to 9,
so that they are bold and red.... or basically how can I select the whole or
part of a row so I can format it ?

thanks for your help ... Roger

How can I format the last row !
 

Cleaning up your cleaning up<g....

Sub TEST()

Dim lngLastRow As Long

With Sheets("Sheet2")
lngLastRow = .Cells(Rows.Count, "B").End(xlUp).Row + 2
.Cells(lngLastRow, 5).Value = "Total"
.Cells(lngLastRow, 8).Formula = "=sum(H2:H" & (lngLastRow - 2) & ")"
.Cells(lngLastRow, 9).Formula = "=sum(I2:I" & (lngLastRow - 2) & ")"
End With

End Sub

--
Rick (MVP - Excel)


"RyanH" wrote in message
...
I took the liberty of cleaning up your code a bit. I would give this a
try.
It should do the same thing. If not, let me know.

Option Explicit

Sub TEST()

Dim lngLastRow As Long

With Sheets("Report(metArb)")
lngLastRow = .Cells(Rows.Count, "B").End(xlDown).Row + 2
.Cells(lngLastRow, 5).Value = "Total"
.Cells(lngLastRow, 8).Formula = "=sum(H2:H" & lngLastRow & ")"
.Cells(lngLastRow, 9).Formula = "=sum(I2:I" & lngLastRow & ")"
End With

End Sub

Hope this helps! If so, let me know and click "YES" below.
--
Cheers,
Ryan


"Ro477" wrote:

I am using Excel2002sp3 on WinXP. I have written

Worksheets("Report(metArb)").Activate
Range("B3").Select
Selection.End(xlDown).Select
lastfilledrow = ActiveCell.Row
ActiveSheet.Cells(lastfilledrow + 2, 5).Value = "Totaal"
sumcellformula = "=sum(h2:h" & lastfilledrow & ")"
ActiveSheet.Cells(lastfilledrow + 2, 8).Formula = sumcellformula
sumcellformula = "=sum(i2:i" & lastfilledrow & ")"
ActiveSheet.Cells(lastfilledrow + 2, 9).Formula = sumcellformula
sumcellformula = "=sum(j2:j" & lastfilledrow & ")"
End Sub

to find the last row and make some totals. This seems to work okay, but I
am
having trouble selecting the lastfilledrow+2 ... that is selecting the
whole
row. I'm sure it is simple, but I can't for the love of me figure out how
to
do it. Basically .... how can I format the lastfilledrow+2, columns 5 to
9,
so that they are bold and red.... or basically how can I select the whole
or
part of a row so I can format it ?

thanks for your help ... Roger

How can I format the last row !
 

Ryan, thanks, very helpful. But there is no YES to click at the foot of your
reply, sorry ... but just the same thanks for your reply ... Roger


"RyanH" wrote in message
...
I took the liberty of cleaning up your code a bit. I would give this a
try.
It should do the same thing. If not, let me know.

Option Explicit

Sub TEST()

Dim lngLastRow As Long

With Sheets("Report(metArb)")
lngLastRow = .Cells(Rows.Count, "B").End(xlDown).Row + 2
.Cells(lngLastRow, 5).Value = "Total"
.Cells(lngLastRow, 8).Formula = "=sum(H2:H" & lngLastRow & ")"
.Cells(lngLastRow, 9).Formula = "=sum(I2:I" & lngLastRow & ")"
End With

End Sub

Hope this helps! If so, let me know and click "YES" below.
--
Cheers,
Ryan


"Ro477" wrote:

I am using Excel2002sp3 on WinXP. I have written

Worksheets("Report(metArb)").Activate
Range("B3").Select
Selection.End(xlDown).Select
lastfilledrow = ActiveCell.Row
ActiveSheet.Cells(lastfilledrow + 2, 5).Value = "Totaal"
sumcellformula = "=sum(h2:h" & lastfilledrow & ")"
ActiveSheet.Cells(lastfilledrow + 2, 8).Formula = sumcellformula
sumcellformula = "=sum(i2:i" & lastfilledrow & ")"
ActiveSheet.Cells(lastfilledrow + 2, 9).Formula = sumcellformula
sumcellformula = "=sum(j2:j" & lastfilledrow & ")"
End Sub

to find the last row and make some totals. This seems to work okay, but I
am
having trouble selecting the lastfilledrow+2 ... that is selecting the
whole
row. I'm sure it is simple, but I can't for the love of me figure out how
to
do it. Basically .... how can I format the lastfilledrow+2, columns 5 to
9,
so that they are bold and red.... or basically how can I select the whole
or
part of a row so I can format it ?

thanks for your help ... Roger