Range issue

[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

 

[Mike H]

Hi,

Without looking too deeply I suggest a good first move would be to Dim those
variable as LONG instead of INTEGER and to see why look at data type
declaration in Excel help.

Mike

 

[Stanley Braverman]

Thank You Mike. That did the trick.

Stan

Hi,

Without looking too deeply I suggest a good first move would be to Dim
those
variable as LONG instead of INTEGER and to see why look at data type
declaration in Excel help.

Mike

 

[Mike H]

Glad I could help

Thank You Mike. That did the trick.

Stan