Insert a subtotal when value in column changes

[ijswalker]

Hi

I have a set of data in a spreadsheet that I want to insert a 2 blank lines and then subtotal the data each time there is a change in value in column F. I then want to grand total at the bottom. I would like to be able to do this using VBA as the subtotal function is not appropriate for my needs.

Can someone help?

Thanks

Ian

 

[chergh]

Well you want to use sheetchange event of the workbook with an if statement so it only fires when column f is changed.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

if target.column = 6 then
   
   code goes here

end if


End Sub

 

[ijswalker]

Hi

Here is the code I have. This is from a previous post and is what I really want it to do in order to insert lines but it does not seem to work.

Is there something missing?

Sub InsertRows()
Sheets("MainViewTest").Select ' adjust sheet   name as necessary
For lRow = lLastRow To 2 Step -1  'Work from last row up to row 2
    If Cells(lRow, 9) <> Cells(lRow - 1, 9) Then
    Rows(lRow).Insert
    End If
Next lRow
End Sub

Thanks

Ian

 

[chergh]

Where is the value for 'lLastRow' coming from?

 

[ijswalker]

Hi,

I think that is the issue. I'll see if I can go from here.

Thanks

Ian

 

[chergh]

WEll to get a value for lLastRow I would use:

lLastRow = thisworkbook.worksheets("MainViewTest").Range("F65536").end(xlup).row

 

[ijswalker]

Thanks Chergh,

That works. Do you have any insight into how I would then add subtotals using VBA?

Thanks

Ian

 

[chergh]

Yeah just sum between the empty cells pretty much. I can think of plenty of ways to do this but nothing particularly elegant might need to leave this till the morning when my brain hasn't ground to a halt.

 

[ijswalker]

Thanks,

I'd appreciate that very much

Ian