How could i create an undo button that would undo all of this code?
Private Sub CommandButton2_Click()
Columns("A:H").Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6, 7,
8), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
range("A1").Select
Call DeleteBlankRows_2
Dim rng As range, cell As range
Set rng = Columns(1).SpecialCells(xlConstants)
Dim row As Long
For Each cell In rng
row = cell.row
If row 1 Then
cell.Offset(0, 8).Formula = _
"=sum(F" & row & ":H" & row & ")"
End If
Next cell
End Sub
"Mat P:son" wrote:

"enyaw" wrote:

I want to insert a formula into a row if the first cell in the row has
something in it. I need to check through a range of cells but the range of
cells will be different every time. How would i program this?

It depends -- how will you define the different ranges? If you can do it
manually, then the following code will do the trick. (If you need to use some
automatic selection, however, you need to specify exactly how you want things
done otherwise we can't help you.)

To use the following: put the code into e.g. the ThisWorkbook code module.
Then select a range, run the macro, and that's it. The way the macro
currently works is that it simply outputs some Square Root functions in
column C, but you can obviously change that very easily to what ever function
you want, and whatever column...

Cheers,
/MP

=============================

Option Explicit

' TODO:
' To which column do you want the formulae
' to be output? I've randomly chosen col C
Private Const TargetColumn As Integer = 3

' You said col A should be used to determine
' whether or not a row should get a formula.
Private Const DecisionColumn As Integer = 1

' This is the main method. Select a range,
' and invoke this method to fill in cells.
Private Sub FillInFuncs()
If (Selection Is Nothing) Then Exit Sub

FillInFuncsHelper _
Selection.Rows(1).row, _
Selection.Rows(Selection.Rows.Count).row
End Sub

' A little helper to do the actual job.
' If you want to change the way we get
' the first and last row, you'll change
' FillInFuncs and leave this one as is.
Private Sub FillInFuncsHelper(lFirstRow As Long, lLastRow As Long)
Dim row As Long
For row = lFirstRow To lLastRow
If Len(Cells(row, DecisionColumn).Value) 0 Then
' TODO: Change the formula into something more suitable
Cells(row, TargetColumn).FormulaR1C1 = "=SQRT(RC1)"
End If
Next row
End Sub

=============================