topic How can i Convert selected cell range to text in Excel by code stage? in Digital Exchange

How can i Convert selected cell range to text in Excel by code stage?

Karthik.choppadandi — Sun, 04 Aug 2024 18:56:42 GMT

Hi All,

I have 40K rows of data in excel after applying the vlookup formulae. I want to Convert selected cell range to text ,

I tried with the below vba codes, But which is veryslow.. Can someone please help me with the code which works fast..

Dim ws as Object = GetWorksheet(handle,workbook,worksheet)
Dim cell As Object
Dim rowRange AS Object
Dim rowNum As Long

ws = GetWorkSheet(Handle, Workbook, Worksheet)
ws.Activate()
'Specify the range of rows you want to convert

For rowNum = 1 To rowcount 'ws.UsedRange.Rows.Count
'set the range for the current row
rowRange = ws.Rows(rowNum)

'Loop through each cell in the cell

For Each cell In rowRange.cells
'check if the cell contains a formula
If cell.HasFormula Then
'Replace the formula with it's current value as text
cell.Value = cell.Value
End If
Next cell
Next rowNum

Re: How can i Convert selected cell range to text in Excel by code stage?

Parthiban_Viatris24 — Fri, 09 Aug 2024 09:22:38 GMT

This code should convert your 40K rows of data from formulas to plain text much faster than the original method you tried. You can optimize the code by avoiding unnecessary loops and making use of Excel's native functions.

Sub ConvertRangeToText()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim dataArray As Variant
Dim rowNum As Long, colNum As Long

' Set the worksheet and the range you want to convert
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
Set rng = ws.UsedRange ' Change to the specific range if needed

' Turn off screen updating and calculation to speed up the process
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' Read the data into a variant array
dataArray = rng.Value

' Loop through the array and replace formulas with their values
For rowNum = 1 To UBound(dataArray, 1)
For colNum = 1 To UBound(dataArray, 2)
If Left(CStr(rng.Cells(rowNum, colNum).Formula), 1) = "=" Then
dataArray(rowNum, colNum) = rng.Cells(rowNum, colNum).Text
End If
Next colNum
Next rowNum

' Write the array back to the range
rng.Value = dataArray

' Turn on screen updating and calculation
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

MsgBox "Conversion to text completed!"
End Sub

Re: How can i Convert selected cell range to text in Excel by code stage?

michaeloneil — Fri, 09 Aug 2024 10:49:27 GMT

Hi @Karthik.choppadandi

I think a code stage might be an overly complicated solution, the easiest thing to do would be to copy and paste the rows as values. If your formula has produced results and you want now only the values from the formulas then you can select all the rows you need and then copy and paste either into the same rows as they currently are into new cells and in this action select values for the paste option. example below