Macro Help - Excel

[GoBlue63]

Can anyone help me write a macro that tells excel to delete any text that is
between brackets? IE - [[xx]] (delete the xx when macro is run)? I need to
make 2 versions of a spreadsheet - one with text with brackets and one where
the brackets remain but the text is deleted....anyone? Maybe a Visual basic
Editor person??

Thanks!

 

[Ron de Bruin]

Hi GoBlue63

Example for column A


Sub Delete_with_Autofilter()
Dim DeleteValue As String
Dim rng As Range
Dim calcmode As Long

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Fill in the value that you want to delete
'Tip: use DeleteValue = "<>ron" to delete rows without ron
DeleteValue = "[*]"

'Sheet with the data, you can also use Sheets("MySheet")
With ActiveSheet

'Firstly, remove the AutoFilter
.AutoFilterMode = False

'Apply the filter
.Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=DeleteValue

With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With

'Remove the AutoFilter
.AutoFilterMode = False
End With

With Application
.ScreenUpdating = True
.Calculation = calcmode
End With

End Sub

 

[Ron de Bruin]

Btw: In this example I assume that A1 is the header of the column

More code on this page
http://www.rondebruin.nl/delete.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Hi GoBlue63

Example for column A


Sub Delete_with_Autofilter()
Dim DeleteValue As String
Dim rng As Range
Dim calcmode As Long

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Fill in the value that you want to delete
'Tip: use DeleteValue = "<>ron" to delete rows without ron
DeleteValue = "[*]"

'Sheet with the data, you can also use Sheets("MySheet")
With ActiveSheet

'Firstly, remove the AutoFilter
.AutoFilterMode = False

'Apply the filter
.Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=DeleteValue

With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With

'Remove the AutoFilter
.AutoFilterMode = False
End With

With Application
.ScreenUpdating = True
.Calculation = calcmode
End With

End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Can anyone help me write a macro that tells excel to delete any text that is
between brackets? IE - [[xx]] (delete the xx when macro is run)? I need to
make 2 versions of a spreadsheet - one with text with brackets and one where
the brackets remain but the text is deleted....anyone? Maybe a Visual basic
Editor person??

Thanks!