I am having one issue. I need to get this to work on a large range all at once. However it keeps crashing. I need it to work on this range :
Range("B2:B" & finalrow(wsWorking))
I tried it this way:
Range("B2:B" & finalrow(wsWorking)).Value = Part(Range("B2:B" & finalrow(wsWorking)))
and this way:
With Range("B2:B" & finalrow(wsWorking)).FormulaR1C1 = "=PART(RC[-1]"
and they both crashed. Finalrow is a function that I have defined to find the final row in a spreadsheet and wsWorksheet is a variable for a worksheet. Here is the code for finalrow in case you want to look at it:
Function finalrow(ByVal shtToCount As Worksheet) As Long
Dim rowTest As Long, columnTest As Integer, finalRowLast As Long, lastColumn As Integer, lgRepeat As Byte
'Determines Final row in Sheet
lastColumn = shtToCount.Cells(1, Columns.Count).End(xlToLeft).Column
finalrow = shtToCount.Cells(Rows.Count, 1).End(xlUp).Row
finalRowLast = shtToCount.Cells(Rows.Count, lastColumn).End(xlUp).Row
If finalRowLast > finalrow Then finalrow = finalRowLast
'loop to find Actual Final row
For lgRepeat = 1 To 2
columnTest = shtToCount.Cells(finalrow + 1, 1).End(xlToRight).Column
rowTest = shtToCount.Cells(Rows.Count, columnTest).End(xlUp).Row
If rowTest > finalrow Then finalrow = rowTest
Next
End Function