Range Total

[Himansu]

Hello everyone,

Anyone know why I'm not get the correct totals here:
--
Dim iLastrow As Long
iLastrow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row

Range("B3").Select
Selection.End(xlDown)(3).Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[" & iLastrow & "]C)"

* e.g.: if I start from b3 to b138; b140 gets selected, but the subtotal is
b141:b142
** it should be b3:b138
--

Also how can I repeat this for muliple columns? If I have 10 columns then
the
sub-total should be done 10 times.

--

Any help will be greatly appreciated.

Have a goog day.
Himansu

 

[Bob Phillips]

Dim iLastrow As Long
Dim icol As Long
Dim i As Long

With ActiveCell
For i = 1 To 10
icol = .Column + i - 1
iLastrow = Cells(Rows.Count, icol).End(xlUp).Row
If iLastrow > ActiveCell.Row Then
Cells(iLastrow + 1, icol).FormulaR1C1 = _
"=SUBTOTAL(9,R2C" & icol & ":R" & iLastrow & "C" & icol &
")"
End If
Next i
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Himansu]

Bob-- thanks this worked. Is it possible to make the values BOLD within the
cells?
Maybe like:

Cells(iLastrow + 1, icol).FormulaR1C1.font.bold = true

Dim iLastrow As Long
Dim icol As Long
Dim i As Long

With ActiveCell
For i = 1 To 10
icol = .Column + i - 1
iLastrow = Cells(Rows.Count, icol).End(xlUp).Row
If iLastrow > ActiveCell.Row Then
Cells(iLastrow + 1, icol).FormulaR1C1 = _
"=SUBTOTAL(9,R2C" & icol & ":R" & iLastrow & "C" & icol &
")"
End If
Next i
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)

Hello everyone,

Anyone know why I'm not get the correct totals here:
--
Dim iLastrow As Long
iLastrow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row

Range("B3").Select
Selection.End(xlDown)(3).Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[" & iLastrow & "]C)"

* e.g.: if I start from b3 to b138; b140 gets selected, but the subtotal is
b141:b142
** it should be b3:b138
--

Also how can I repeat this for muliple columns? If I have 10 columns then
the
sub-total should be done 10 times.

--

Any help will be greatly appreciated.

Have a goog day.
Himansu

 

[Tom Ogilvy]

Cells(iLastrow + 1, icol).font.bold = True

--
Regards,
Tom Ogilvy

Bob-- thanks this worked. Is it possible to make the values BOLD within the
cells?
Maybe like:

Dim iLastrow As Long
Dim icol As Long
Dim i As Long

With ActiveCell
For i = 1 To 10
icol = .Column + i - 1
iLastrow = Cells(Rows.Count, icol).End(xlUp).Row
If iLastrow > ActiveCell.Row Then
Cells(iLastrow + 1, icol).FormulaR1C1 = _
"=SUBTOTAL(9,R2C" & icol & ":R" & iLastrow & "C" & icol &
")"
End If
Next i
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)

Hello everyone,

Anyone know why I'm not get the correct totals here:
--
Dim iLastrow As Long
iLastrow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row

Range("B3").Select
Selection.End(xlDown)(3).Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[" & iLastrow & "]C)"

* e.g.: if I start from b3 to b138; b140 gets selected, but the

subtotal
is

b141:b142
** it should be b3:b138
--

Also how can I repeat this for muliple columns? If I have 10 columns then
the
sub-total should be done 10 times.

--

Any help will be greatly appreciated.

Have a goog day.
Himansu

 

[Himansu]

Excellent. You guys rock. I'm starting to REALLY REALLY
appreciate VB in Excel.

Cells(iLastrow + 1, icol).font.bold = True

--
Regards,
Tom Ogilvy

Bob-- thanks this worked. Is it possible to make the values BOLD within the
cells?
Maybe like:

icol

&

")"
End If
Next i
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


Hello everyone,

Anyone know why I'm not get the correct totals here:
--
Dim iLastrow As Long
iLastrow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row

Range("B3").Select
Selection.End(xlDown)(3).Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[" & iLastrow & "]C)"

* e.g.: if I start from b3 to b138; b140 gets selected, but the subtotal
is
b141:b142
** it should be b3:b138

columns
then

the
sub-total should be done 10 times.

--

Any help will be greatly appreciated.

Have a goog day.
Himansu