S
Stanley Braverman
This macro used to work correctly. Now that the rows have increased to over
33,000. The macro no longer execute the last command of splitting the last
two coulombs. Any help would be appreciated.
Public Sub DeDash()
Dim iNumRows As Integer
Dim i As Integer
Dim j As Integer
On Error Resume Next
With ThisWorkbook.Sheets(1)
' Delete columns A, B, C, G and H
.Range("A:C,G:H").Delete Shift:=xlToLeft
' Align left columns A, B and C
.Columns("A:C").HorizontalAlignment = xlLeft
' Autofit columns A, B and C
.Columns("A:C").EntireColumn.AutoFit
iNumRows = .Range("A1").CurrentRegion.Rows.Count
For i = 1 To iNumRows
' Replace dash with space in columns A and B
.Cells(i, 1).Value = Trim(Replace(.Cells(i, 1).Value, "-", " "))
.Cells(i, 2).Value = Trim(Replace(.Cells(i, 2).Value, "-", " "))
' Split column C on dash
j = InStr(1, .Cells(i, 3).Value, "-")
If j <> 0 Then
.Cells(i, 4).Value = Mid(.Cells(i, 3).Value, j + 1)
.Cells(i, 3).Value = Left(.Cells(i, 3).Value, j - 1)
End If
Next i
' Delete first row
.Rows("1:1").Delete Shift:=xlUp
End With
End Sub
33,000. The macro no longer execute the last command of splitting the last
two coulombs. Any help would be appreciated.
Public Sub DeDash()
Dim iNumRows As Integer
Dim i As Integer
Dim j As Integer
On Error Resume Next
With ThisWorkbook.Sheets(1)
' Delete columns A, B, C, G and H
.Range("A:C,G:H").Delete Shift:=xlToLeft
' Align left columns A, B and C
.Columns("A:C").HorizontalAlignment = xlLeft
' Autofit columns A, B and C
.Columns("A:C").EntireColumn.AutoFit
iNumRows = .Range("A1").CurrentRegion.Rows.Count
For i = 1 To iNumRows
' Replace dash with space in columns A and B
.Cells(i, 1).Value = Trim(Replace(.Cells(i, 1).Value, "-", " "))
.Cells(i, 2).Value = Trim(Replace(.Cells(i, 2).Value, "-", " "))
' Split column C on dash
j = InStr(1, .Cells(i, 3).Value, "-")
If j <> 0 Then
.Cells(i, 4).Value = Mid(.Cells(i, 3).Value, j + 1)
.Cells(i, 3).Value = Left(.Cells(i, 3).Value, j - 1)
End If
Next i
' Delete first row
.Rows("1:1").Delete Shift:=xlUp
End With
End Sub