• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 648
  • Last Modified:

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
0
JimNPacha
Asked:
JimNPacha
1 Solution
 
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
 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now