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]

The block of text is always the same verbiage and size.

 

[JoeMo]

EDIT: Just saw your 2nd post. This assumes that each line of the "block" is in a separate row and the block is in col A, below all other data in col A.

Sub BeatTheBlock()
'assume block is in col A - change range to suit
Dim lR As Long, lastCell As Range, firstCell As Range
Set lastCell = Cells(Rows.Count, "A").End(xlUp)  'change column to suit
Set firstCell = lastCell.End(xlUp)
Range(firstCell, lastCell).ClearContents
End Sub

 

[Michael M]

Maybe this, assuming data is in col "A"

Sub MM2()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 5 Step -1
    If InStr(Range("A" & r).Value, "*My") Then
    Range("A" & r).Resize(5).EntireRow.Delete
    End If
Next r
End Sub

 

[Rick Rothstein]

Also assuming data is in Column A, here is one more to consider...

Sub BeatTheBlock2()
  Dim Ar As Range
  Set Ar = Columns("A").SpecialCells(xlConstants)
  Ar.Areas(Ar.Areas.Count).Clear
End Sub

 

[sodapop]

Thanks JoeMo it works great. I am trying to write a macro that will convert a table to a non-table format. When I do it manually it works but the macro doesn't clear the dropdown arrows. And that is the first step in cleaning up the report.


Sub sm_table_removal()
'
' sm_table_removal Macro
'

'
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$H$43"), , xlYes).Name = _
"Table1"
Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = ""
Range("A3").Select
End Sub

 

[Rick Rothstein]

I am trying to write a macro that will convert a table to a non-table format.

Does this macro do what you want...

Sub ConvertTableToRange()
  Dim StartCell As Range, Data As Variant
  With ActiveSheet.ListObjects("Table1")
    Set StartCell = .Range(1)
    Data = .Range.Formula
    .Delete
    StartCell.Resize(UBound(Data, 1), UBound(Data, 2)) = Data
  End With
End Sub

 

[sodapop]

Thanks for the reply Rick,

I will try this out this weekend.

 

[sodapop]

Hi @Rick Rothstein,

Sorry for the slow response.

The code hung up at ("Table1"). I am assuming that I need to rename Table1 to something different but I am not sure what the name should be.

 

[Rick Rothstein]

The code hung up at ("Table1"). I am assuming that I need to rename Table1 to something different but I am not sure what the name should be.

Select any cell within your table... you should get a new menu tab at the top labeled "Table Tools - Design"... select that tab if it is not automatically selected and look to the left of the ribbon... your table name should be listed there... use exactly what it says the table's name is.