Can someone help me out here with a simple macro?

I am trying to code a macro inside MS-Excel 2003 to delete these: [Left and Right] brackets and anything inside them.

Thank you guys!
-Brian

Sub delSquareBKT()
Dim locStart, locEnd As Integer
Dim strLeft, strRight As String

If InStr(ActiveCell.Text, "[") <> 0 And InStr(ActiveCell.Text, "]") <> 0 Then
locStart = InStr(1, ActiveCell.Text, "[", vbTextCompare)
locEnd = InStr(1, ActiveCell.Text, "]", vbTextCompare)
   strLeft = Left(ActiveCell.Text, locStart - 1)
   strRight = Right(ActiveCell.Text, Len(ActiveCell.Text) - locEnd)
ActiveCell.Value = strLeft & strRight
End If
End Sub

Thanks alot!

Only one prob. it's not working with multiple cells selected, I select only one cell at a time, and no problem it works, but if i select all cells that i need to run the macro on, it don't want to work.

You can try recording a macro and performing the actions that you want.
It will all be recording and then you can look at the VB code and make any additions/modification.

Originally Posted by Frostydasnowman

Thanks alot!

Only one prob. it's not working with multiple cells selected, I select only one cell at a time, and no problem it works, but if i select all cells that i need to run the macro on, it don't want to work.

Correct.
It's only written to work on the contents of the active cell. To work on a selected range it would need to loop and move to each cell in the group.

Would the selection be in rows, columns or both ?

Correct.
It's only written to work on the contents of the active cell. To work on a selected range it would need to loop and move to each cell in the group.

Would the selection be in rows, columns or both ?

I need it to work in columns (going straight dow ex: all records in column A (A1 to A500 for example)) not rows going across.

Thank you Chrishirst!

No problem.

recursive subroutines needed in that case.

Sub delSquareBKT()
Dim locStart, locEnd As Integer
Dim strLeft, strRight As String

If InStr(ActiveCell.Text, "[") <> 0 And InStr(ActiveCell.Text, "]") <> 0 Then
locStart = InStr(1, ActiveCell.Text, "[", vbTextCompare)
locEnd = InStr(1, ActiveCell.Text, "]", vbTextCompare)
   strLeft = Left(ActiveCell.Text, locStart - 1)
   strRight = Right(ActiveCell.Text, Len(ActiveCell.Text) - locEnd)
ActiveCell.Value = strLeft & strRight
End If
moveNext
End Sub

Sub moveNext()
If ActiveCell.Text <> "" Then
    Selection.Offset(1, 0).Activate
    delSquareBKT
End If
End Sub

select the topmost cell and call the delSquareBKT sub, it will then run down the column until it finds an empty cell, where it will end.

Hi Chrishirst,

The macro does work this time only that it stops unexpectedly with the following error:

Error 28: Out of Stack Space

You can see this page for a fix, I can't seem to figure it out

"Not Enough Stack Space to Run Macro"

Thanks in advance!

It will be too many recursive calls

I tested it on 1500 cells on Excel 2000 and it ran ok, how many cells are in the column you are running it against?

a bit of tweaking to remove the recursion should fix it though.

Sub delSquareBKT()
Dim locStart, locEnd As Integer
Dim strLeft, strRight As String

While ActiveCell.Text <> ""
If InStr(ActiveCell.Text, "[") <> 0 And InStr(ActiveCell.Text, "]") <> 0 Then
locStart = InStr(1, ActiveCell.Text, "[", vbTextCompare)
locEnd = InStr(1, ActiveCell.Text, "]", vbTextCompare)
   strLeft = Left(ActiveCell.Text, locStart - 1)
   strRight = Right(ActiveCell.Text, Len(ActiveCell.Text) - locEnd)
ActiveCell.Value = strLeft & strRight
End If
Selection.Offset(1, 0).Activate
Wend

End Sub

single sub with a while/wend loop, ran on 9000 + cells with no bother.

You can perform this task with a simple find and replace.

Just put [*] as the find text and leave the replace text empty.

A macro for it looks like the following:

Selection.Replace What:="[*]", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False