VBA to add quotation marks around all cell values in a sheet?

[XcelNoobster]

So I am looking to create a macro that adds quotations marks around all cell values in a sheet. How would I do that?

 

[Domenic]

The following macro assumes that the worksheet containing the data is the active sheet, and that the data starts at cell A1...

Sub AddQuotes()

    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
   
    Dim lastCol As Long
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
   
    Dim rng As Range
    Set rng = Range("A1", Cells(lastRow, lastCol))
   
    Dim cell As Range
    For Each cell In rng
        cell.Value = """" & cell.Value & """"
    Next cell
   
End Sub

By the way, I would suggest that you update your profile to include the version of Excel you're using, as it may help in future in providing the most efficient solution.

Hope this helps!

 

[snjpverma]

If the data in the worksheet goes beyond the row and column limits of A1, then the range defined by Domenic's line of code may not include all the cells with data in the worksheet.

To ensure that all cells with data are included in the range, you can use the UsedRange property of the worksheet, as shown in below code.
I'm not a VBA expert, so try this in a copy of your workbook.

Sub AddQuotesToUsedRange()
    Dim cell As Range
    Dim myRange As Range
    
    Set myRange = ActiveSheet.UsedRange
    
    For Each cell In myRange
        If Not IsEmpty(cell.Value) Then
            cell.Value = Chr(34) & cell.Value & Chr(34)
        End If
    Next cell
End Sub

Alternatively, you can use the find function to define the last row and column.