Range issue

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
 
M

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
 
S

Stanley Braverman

Thank You Mike. That did the trick.

Stan
Mike H said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top