PDA

View Full Version : [SOLVED] Change the range to all data range in column



parscon
09-23-2014, 07:50 AM
I have code that will remove the comma at start and end of string but it will be work on rage D1:D40000 and i need check all the row of the column that has data maybe 200000 and etc ,,,
So how can i change this code to check all the data on column D

Thank uoi



Sub Replacer2()
Dim rCell As Range, strChar As String
strChar = ","
For Each rCell In Worksheets("Sheet2").Range("D1:D40000").Cells
If Left(rCell.Value, 1) = strChar Then
rCell.Value = Mid(rCell.Value, 2, Len(rCell.Value) - 1)
ElseIf Right(rCell.Value, 1) = strChar Then
rCell.Value = Left(rCell.Value, Len(rCell.Value) - 1)
End If
Next rCell
End Sub

Paul_Hossler
09-23-2014, 10:34 AM
I'd do something like this



Option Explicit
Sub Replacer2()
Dim rCell As Range, strChar As String
Dim rConstantText As Range
strChar = ","

'only get the cells in col D that have constants and text in them
Set rConstantText = Nothing
On Error Resume Next
Set rConstantText = Worksheets("Sheet2").Columns(4).SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

'if there aren't any then get out
If rConstantText Is Nothing Then Exit Sub

Application.ScreenUpdating = False

'for each of the cells
'this removes a comma from the front and/or end
For Each rCell In rConstantText.Cells
With rCell
Application.StatusBar = "Checking " & .Address
If Left(.Value, 1) = strChar Then .Value = Mid(.Value, 2, Len(.Value) - 1)
If Right(.Value, 1) = strChar Then .Value = Left(.Value, Len(.Value) - 1)
End With
Next rCell
Application.ScreenUpdating = False
Application.StatusBar = False
End Sub

parscon
10-02-2014, 03:26 AM
Thank you very much.