Finding and working with rows that have data in only
 

Good morning,

I am trying to design a macro for a workbook that contains transactions over
a period of time that groups together, and summarises, all the transactions
that occured at the same time.

EG: (workbook before macro)
A B C
1 Date quantity price
2 29/07/09 10 10
3 29/07/09 20 10
4 30/07/09 10 10
5 30/07/09 20 10

(workbook after macro)
A B C
D
1 Date quantity price
*newinsertedcolumn*sales
2 29/07/09 10 10
100
3 29/07/09 20 10
200
4 30 10
300
5
6 30/07/09 10 10
100
7 30/07/09 20 10
200
8 30 10
300

**Where Column D is equal to quantity x price**

I have learned how to insert the blank rows to seperate the different dates,
While checkrow < lastrow
If Cells(checkrow, datecolumn) < Cells(checkrow + 1, datecolumn) Then
Rows(checkrow + 1).EntireRow.Insert
Rows(checkrow + 1).EntireRow.Insert
checkrow = checkrow + 3
lastrow = lastrow + 2
Else: checkrow = checkrow + 1
End If

I can insert the new column
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
(there are further columns after D, hence why I needed to insert it)

But how can i program the macro to then look for only rows with transactions
in, and perform the calculation of Quantity x Price and then calculate the
total of those results that are grouped by time?

If I haven't been clear in my question, please just ask for more detail and
I will provide as best I can.

I really appreciate the help you guys provide on this forum, and look
forward to the responses.

Regards and thanks once again
Paul

Finding and working with rows that have data in only
 

SORRY FOR THE FORMATTING PROBLEM IN THE INITIAL POST... Have tried to clean
it up

Good morning,

I am trying to design a macro for a workbook that contains transactions over
a period of time that groups together, and summarises, all the transactions
that occured at the same time.

EG: (workbook before macro)
A B C
1 Date quantity price
2 29/07/09 10 10
3 29/07/09 20 10
4 30/07/09 10 10
5 30/07/09 20 10

(workbook after macro)
A B C D
1 Date quantity price *newinsertedcolumn*sales
2 29/07/09 10 10 100
3 29/07/09 20 10 200
4 30 10 300
5
6 30/07/09 10 10 100
7 30/07/09 20 10 200
8 30 10 300

**Where Column D is equal to quantity x price**

I have learned how to insert the blank rows to seperate the different dates,
While checkrow < lastrow
If Cells(checkrow, datecolumn) < Cells(checkrow + 1, datecolumn) Then
Rows(checkrow + 1).EntireRow.Insert
Rows(checkrow + 1).EntireRow.Insert
checkrow = checkrow + 3
lastrow = lastrow + 2
Else: checkrow = checkrow + 1
End If

I can insert the new column
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
(there are further columns after D, hence why I needed to insert it)

But how can i program the macro to then look for only rows with transactions
in, and perform the calculation of Quantity x Price and then calculate the
total of those results that are grouped by time?

If I haven't been clear in my question, please just ask for more detail and
I will provide as best I can.

I really appreciate the help you guys provide on this forum, and look
forward to the responses.

Regards and thanks once again
Paul

Finding and working with rows that have data in only
 

Just recorded this simple macro. Does it doe what you want?

Sub Macro1()
Range("A1").Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
End Sub


HTH,
Ryan----

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"PVANS" wrote:

SORRY FOR THE FORMATTING PROBLEM IN THE INITIAL POST... Have tried to clean
it up

Good morning,

I am trying to design a macro for a workbook that contains transactions over
a period of time that groups together, and summarises, all the transactions
that occured at the same time.

EG: (workbook before macro)
A B C
1 Date quantity price
2 29/07/09 10 10
3 29/07/09 20 10
4 30/07/09 10 10
5 30/07/09 20 10

(workbook after macro)
A B C D
1 Date quantity price *newinsertedcolumn*sales
2 29/07/09 10 10 100
3 29/07/09 20 10 200
4 30 10 300
5
6 30/07/09 10 10 100
7 30/07/09 20 10 200
8 30 10 300

**Where Column D is equal to quantity x price**

I have learned how to insert the blank rows to seperate the different dates,
While checkrow < lastrow
If Cells(checkrow, datecolumn) < Cells(checkrow + 1, datecolumn) Then
Rows(checkrow + 1).EntireRow.Insert
Rows(checkrow + 1).EntireRow.Insert
checkrow = checkrow + 3
lastrow = lastrow + 2
Else: checkrow = checkrow + 1
End If

I can insert the new column
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
(there are further columns after D, hence why I needed to insert it)

But how can i program the macro to then look for only rows with transactions
in, and perform the calculation of Quantity x Price and then calculate the
total of those results that are grouped by time?

If I haven't been clear in my question, please just ask for more detail and
I will provide as best I can.

I really appreciate the help you guys provide on this forum, and look
forward to the responses.

Regards and thanks once again
Paul