I have a macro that copies information from several worksheets into one
worksheet. After the information is copied the macro sorts the new worksheet
so that blank rows are moved to the end. I want to code the macro to also
find the first blank row (after the copied data) and give me totals. How do
I do this?

Thank you.

Sub dread()
Dim r As Range, rr As Range
Set r = ActiveSheet.UsedRange
Dim l As Long, k As Long
l = r.Rows.Count + r.Row

For k = 1 To l
Set rr = Range(Cells(k, 1), Cells(k, 256))
If Application.CountA(rr) = 0 Then
Exit For
End If
Next


For i = 1 To 256
Cells(k, i).Value = 0
For kk = 1 To k - 1
Cells(k, i).Value = Cells(k, i).Value + Cells(kk, i).Value
Next
Next
End Sub

Will locate the first empty row and then fill that row with the sum of the
rows above it.
--
Gary''s Student


"dread" wrote:

I have a macro that copies information from several worksheets into one
worksheet. After the information is copied the macro sorts the new worksheet
so that blank rows are moved to the end. I want to code the macro to also
find the first blank row (after the copied data) and give me totals. How do
I do this?

Thank you.

Hi dread,

You can do this :

Sub Total()
Dim R As Range, TTL As Integer
For Each R In Range("B1:B50") ' change Range("B1:B50") to your range
If R.Value < "" And IsNumeric(R.Value) Then TTL = TTL + R.Value
Next R
MsgBox TTL
End Sub

--

Regards,

Halim


"dread" wrote:

I have a macro that copies information from several worksheets into one
worksheet. After the information is copied the macro sorts the new worksheet
so that blank rows are moved to the end. I want to code the macro to also
find the first blank row (after the copied data) and give me totals. How do
I do this?

Thank you.

I'm getting a compile error because i is not defined.

"Gary''s Student" wrote:

Sub dread()
Dim r As Range, rr As Range
Set r = ActiveSheet.UsedRange
Dim l As Long, k As Long
l = r.Rows.Count + r.Row

For k = 1 To l
Set rr = Range(Cells(k, 1), Cells(k, 256))
If Application.CountA(rr) = 0 Then
Exit For
End If
Next


For i = 1 To 256
Cells(k, i).Value = 0
For kk = 1 To k - 1
Cells(k, i).Value = Cells(k, i).Value + Cells(kk, i).Value
Next
Next
End Sub

Will locate the first empty row and then fill that row with the sum of the
rows above it.
--
Gary''s Student


"dread" wrote:

I have a macro that copies information from several worksheets into one
worksheet. After the information is copied the macro sorts the new worksheet
so that blank rows are moved to the end. I want to code the macro to also
find the first blank row (after the copied data) and give me totals. How do
I do this?

Thank you.

Hi Halim,

Instead of displaying the total in the MsgBox, how would I code this to
display the total in the first blank row?

Thanks.

"Halim" wrote:

Hi dread,

You can do this :

Sub Total()
Dim R As Range, TTL As Integer
For Each R In Range("B1:B50") ' change Range("B1:B50") to your range
If R.Value < "" And IsNumeric(R.Value) Then TTL = TTL + R.Value
Next R
MsgBox TTL
End Sub

--

Regards,

Halim


"dread" wrote:

I have a macro that copies information from several worksheets into one
worksheet. After the information is copied the macro sorts the new worksheet
so that blank rows are moved to the end. I want to code the macro to also
find the first blank row (after the copied data) and give me totals. How do
I do this?

Thank you.