How to remove a block of text

[sodapop]

Hi,

I have report that puts this block of text after a blank line and I would like to delete it in a macro. The lines of data above are varying length IE 32 rows of data one time then 25 rows the next.

data
data
data

*My Open calls
Copyright (c) 2000-2018 salesforce.com, inc. All rights reserved.
Confidential Information - Do Not Distribute
Generated By: John Q Public 8/9/2018 8:24 PM
XZY Inc.



Thanks for the help.

 

[sodapop]

Thanks, I had to select insert\table then saw the table1. I assumed it was a table from the get go because there are drop down tabs that allow me to select what data per column that I would like to see. I guess I need to put in a couple of steps prior to running your code. I will try this and update the thread in the morning.

Thanks again for the help!

 

[sodapop]

Thanks for the help @Rick Rothstein I am a bonehead. I realized today that what I thought was a table was really just filters. Below is the code for the macro but I would like it to work on every file from Service Max. Currently it sends be to debug at this line. ActiveWorkbook.Worksheets("report1536663136980").Sort.SortFields.Clear

Sub Clean_SM()
'
' Clean_SM Macro
'
' Keyboard Shortcut: Ctrl+Shift+U
'
Selection.AutoFilter
Range("A1").Select
ActiveCell.FormulaR1C1 = "Work Order"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Serial Number"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Contract End Date"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Required Start Date"
Rows("1:1").Select
With Selection
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("G2:I31").Select
Selection.NumberFormat = "mm/dd/yy;@"
Rows("33:38").Select
Selection.Delete Shift:=xlUp
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Columns("E:E").Select
Selection.Replace What:="installation q*", Replacement:="IQ", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="installatio*", Replacement:="INS", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="planned*", Replacement:="PM", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="billa*", Replacement:="BPM", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="quote*", Replacement:="QUO", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Select
Cells.EntireColumn.AutoFit
Range("G5").Select
Columns("G:G").ColumnWidth = 7.57
Columns("H:H").ColumnWidth = 8.71
Columns("I:I").ColumnWidth = 10
Rows("1:1").EntireRow.AutoFit
Columns("G:G").ColumnWidth = 8
Application.WindowState = xlNormal
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]<TODAY()-7,""Late"",IF(RC[-1]<(TODAY()+30),""Due"",""""))"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J31")
Range("J2:J31").Select
Range("C10").Select
ActiveWorkbook.Worksheets("report1536663136980").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("report1536663136980").Sort.SortFields.Add Key:= _
Range("C2:C31"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("report1536663136980").Sort.SortFields.Add Key:= _
Range("I2:I31"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("report1536663136980").Sort
.SetRange Range("A1:J31")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Rows("1:1").RowHeight = 39.75
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("A1:J1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
With Selection.Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("J3").Select
Columns("J:J").EntireColumn.AutoFit
End Sub