Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding time in 24 hour format to produce hours in decimal format | Excel Worksheet Functions | |||
Need help with converting CUSTOM format/TEXT format to DATE format | Excel Worksheet Functions | |||
Replace million-billion number format to lakhs-crores format | Excel Discussion (Misc queries) | |||
how to format excel format to text format with separator "|" in s. | New Users to Excel | |||
Keep format after paste from other worksheets - conditional format or EnableControl solution doesn't work | Excel Programming |