Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help - Excel
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help - Excel
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 "GoBlue63" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help - Excel
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 "Ron de Bruin" wrote in message ... 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 "GoBlue63" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avoid Outlook macro security for Excel bades macro | Excel Programming | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
translate lotus 1-2-3 macro into excel macro using excel 2000 | Excel Programming | |||
Excel Macro Issue Trying to autorun Macro Upon Opening Worksheet | Excel Programming |