Adding brackets to cells that have data in a Column

[Livin404]

Good evening, I'm trying to add brackets to data () in a specific Column C. I had several formulas that nearly worked, one that put brackets around everything including way past rows that had any data. I also had one where I can highlight and select, or fill in the block. I'm looking for one that will put brackets around only cells with data. I tried something like, but that just gets me further away. Thank you,

Sub Add_Brackets ()
With ActiveSheet
        With Range("C1", Range("C" & Rows.Count).End(xlUp))
        cell.Value = "(" & cell.Value & ")"
        On Error Resume Next
End With
End With
End Sub

 

[nej12]

Would ctrl+shift+enter work or try something with concatenate and put brackets in column b and c.

 

[Livin404]

Would ctrl+shift+enter work or try something with concatenate and put brackets in column b and c.

Thanking but I definitely have to use VBA.

 

[mrshl9898]

Sub Add_Brackets()
With ActiveSheet
        For Each cell In Range("C1", Range("C" & Rows.Count).End(xlUp))
        cell.Value = "(" & cell.Value & ")"
        On Error Resume Next
        Next
End With
End Sub

 

[eljorge]

Hello, try this code. (It assumes the data is in column c. You just have to define the starting row

Sub Add_Brackets()
Dim lastRow As Long
Dim usedRange As Range
Const startingRow As Integer = 2 'change starting row here
Dim cell As Range
With ActiveSheet
    'Get last cell with data in column c
    lastRow = .Cells(.Rows.Count, 3).End(xlUp).Row
    Set usedRange = .Range("C" & startingRow & ":C" & lastRow)
    For Each cell In usedRange
        If cell.Value <> "" Then cell.Value = "(" & cell.Value & ")"
    Next
    
    
End With


End Sub

 

[Livin404]

Sub Add_Brackets()
With ActiveSheet
        For Each cell In Range("C1", Range("C" & Rows.Count).End(xlUp))
        cell.Value = "(" & cell.Value & ")"
        On Error Resume Next
        Next
End With
End Sub

That's beautiful thank you,

 

[Livin404]

Thank you so much for your input, I posted a this a little while ago, and I haven't heard anything back you. This formula works great, but I want it to also look for "777" in addition to "CHS". Also for some reason it will not delete the CHS - the (whole row) in the first row. Thank you so much,

Sub Delete_locals()
With ActiveSheet
    .AutoFilterMode = False
    With Range("C1", Range("C" & Rows.Count).End(xlUp))
        .AutoFilter 1, "*CHS*"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With
End Sub

 

[eljorge]

Something like this whould work. (if it doesnt delete the first row, i think you may need to create a header row.)

Sub Delete_locals()
With ActiveSheet
    .AutoFilterMode = False
    With Range("C1", Range("C" & Rows.Count).End(xlUp))
        .AutoFilter 1, "*CHS*"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
        .AutoFilter 1, "*777*"
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With
End Sub

 

[mrshl9898]

I did see that, but had no clever reply. I'd just repeat the process but you may or may not have to remove the **'s (I tested it on 777 alone and had to remove them)

It won't remove the first row because that is the header row,

Sub Delete_locals()
With ActiveSheet
    .AutoFilterMode = False
    With Range("C1", Range("C" & Rows.Count).End(xlUp))
        .AutoFilter 1, "*CHS*"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With

With ActiveSheet
    .AutoFilterMode = False
    With Range("C1", Range("C" & Rows.Count).End(xlUp))
        .AutoFilter 1, "777"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With

End Sub

 

[Livin404]

That's brilliant, I used the ** for the "777" to and is worked as designed. I correct the top CHS I set the offset to 0. Thank you so much.