Excel 2003: VBA "Function" causes "#VALUE!" errors after running "insert/delete row" custom macro

Matt C · #1 01-08-2022, 06:03 AM

Hi. Thanks for this. It half works.

I've added the line as you suggested:

Quote:

Function SumCat(WorkRng As Range)
'

' SumCat Function
'
' Calculates cells formatted as "Total Category"
' Example: =SUMCAT(A1:C9)
'
Dim rng As Range
Dim xSum As Long

Application.Volatile

For Each rng In WorkRng
' Finds cells with "Total Category" stylename
If rng.Style = "Total Category" Then
xSum = xSum + rng.Value
End If

Next

SumCat = xSum

End Function

The "Insert Row" macro (see code in first post) now seems to work fine, but I'm still getting the error after running the "Delete Row" macro (code below).

Quote:

Sub DeleteRow()
'
' DeleteRow Macro
'
response = MsgBox("Are you sure you want to remove this row?", vbYesNo)

If response = vbYes Then

ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete Shift:=xlUp

Else
Exit Sub

End If

End Sub

As before, deleting a row manually does not result in the "#VALUE!" error.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Conditional function to operate a formula based on "Yes" or "No" Condition	Des ONeill	Excel	1	07-03-2021 12:17 AM
Condtional function to operate a formula based on "Yes" or "No"	Des ONeill	Excel	3	07-03-2021 12:16 AM
How do I delete the names under "Exceptions" when I use the "Restrict Editing" feature in Word?	MengS	Word	0	02-25-2015 02:57 PM
remove repeated words with " macro " or " wild cards " in texts with parentheses and commas	jocke321	Word VBA	2	12-10-2014 11:27 AM
How to choose a "List" for certain "Heading" from "Modify" tool?	Jamal NUMAN	Word	2	07-03-2011 03:11 AM