Chris 1672
New Member
- Joined
- Feb 3, 2010
- Messages
- 5
Hi,
Currently I'm completing a macro that will take a list of data and put it in to a report format. I'm still learning macros so are using a combination of recording the steps and then searching for previous macros created to do various steps
i have been able to create the following steps:
(i) Sort data in column B:
Sub Sort()
Range("B2").CurrentRegion.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
(ii) Delete column A:
Sub DeleteA()
'
' DeleteA Macro
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("A2").Select
End Sub
(iii) Insert a row after each new variable in column B and sum values in column D for these variables:
Sub subtots()
Dim sh As Worksheet, lr As Long, i As Long, x As Long, rng As Range, cel As Range
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1
With sh
If .Cells(i, 1).Value = .Cells(i - 1, 1).Value Then
x = x + 1
Else
Set rng = .Cells(i, 4).Resize(x + 1, 1)
Set cel = .Cells(i, 4).Offset(x + 1, 0)
cel.EntireRow.Insert
cel.Offset(-1#) = Application.Sum(rng)
cel.Offset(-1, -3) = cel.Offset(-3, -3).Value & " Total"
x = 0
End If
End With
Next
End Sub
I also want to put a subtotal in column E for the same range as above, however can't figure out how to do it.
Would anyone have any ideas or thoughts, as i'm not understanding the code in point 3.
C
Currently I'm completing a macro that will take a list of data and put it in to a report format. I'm still learning macros so are using a combination of recording the steps and then searching for previous macros created to do various steps
i have been able to create the following steps:
(i) Sort data in column B:
Sub Sort()
Range("B2").CurrentRegion.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
(ii) Delete column A:
Sub DeleteA()
'
' DeleteA Macro
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("A2").Select
End Sub
(iii) Insert a row after each new variable in column B and sum values in column D for these variables:
Sub subtots()
Dim sh As Worksheet, lr As Long, i As Long, x As Long, rng As Range, cel As Range
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1
With sh
If .Cells(i, 1).Value = .Cells(i - 1, 1).Value Then
x = x + 1
Else
Set rng = .Cells(i, 4).Resize(x + 1, 1)
Set cel = .Cells(i, 4).Offset(x + 1, 0)
cel.EntireRow.Insert
cel.Offset(-1#) = Application.Sum(rng)
cel.Offset(-1, -3) = cel.Offset(-3, -3).Value & " Total"
x = 0
End If
End With
Next
End Sub
I also want to put a subtotal in column E for the same range as above, however can't figure out how to do it.
Would anyone have any ideas or thoughts, as i'm not understanding the code in point 3.
C