Finding and working with rows that have data in only

P

PVANS

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
 
R

ryguy7272

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----
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top