MACRO TO DELETE GRAND TOTAL ROW OF SUBTOTALED DATA

MS Excel - 2000

I am Running a Macro to Chart Subtotaled Data, of an indefinite number of rows. But I need to delete the Grand Total Row. In column F, in the very last row (indefinite no. of rows) it reads, "Grand Total". How do I focus on that cell value, in the last row of column F and delete the row ?

NADZAT
JimNPachaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jeveristCommented:
Hi JimNPacha,

You can search for "Grand Total" in column and delete the row like this:

Sub DeleteGrandTotalRow()
Dim ws As Worksheet, frg As Range

Set ws = ActiveSheet

Set frg = ws.Columns("F").Find(What:="Grand Total", LookIn:=xlValues, LookAt:=xlPart)

If Not frg Is Nothing Then
    frg.EntireRow.Delete
End If

End Sub

Jim
0
Saurabh Singh TeotiaCommented:
U Meant this...it will automatically will delete Grand total entry from the last row..irespective where it is...

Sub delete()
Dim A As Long
Application.ScreenUpdating = False

Range("F" & Cells(65536, "f").End(xlUp).Row).Select
A = Cells(65536, "f").End(xlUp).Row
If (UCase(Trim(ActiveCell.Value)) = "GRAND TOTAL") Then
Rows(A).delete
Else
MsgBox ("No Grand Total found")
End If
Range("A1").Select
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
try this

Sub deleteLastRow()
Dim xlObj As Object, lastRow
Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Open "C:\myExcel.xls"
With xlObj
   
    lastRow = .sheets(1).usedrange.rows.Count
    If .range("F" & lastRow).Value = "Grand Total" Then
        .range("F" & lastRow).entirerow.Delete
    End If
    .activeworkbook.Save
End With
    xlObj.Quit
End Sub
0
JimNPachaAuthor Commented:
It works.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.