Need a Macro that Loops and Performs Functions in Excel 2010

I don't know enough about macros to know where to start with this, but here's what I need in the attached file.  Hopefully someone can help me.

On column I, the following needs to be done:

1.  Loop through the column until no values are found
2.  Perform the following functions
     a.  Remove/delete the first 3 characters
     b.  Next, if there is a leading space after deleting the first 3 characters, delete the leading space.
     c.  Then delete the last 8 characters (is a date) plus the space before the date.
Kathy-Kelley-example.xls
esu4236Asked:
Who is Participating?
 
redmondbCommented:
esu4236,

Martin's point about speeding things up is well-made. Depending on the number of rows , there are a number of other speed-boosts which may be worthwhile. (They'll definitely speed things up, but the extra complexity would be pointless for a small file.)

Edit:
Please see the attached. A couple of points...
(1) I noticed that there were two spaces before the date. I'm guessing that you didn't want them, so any spaces immediately before the date are now stripped.
(2) Invalid data no longer causes an error message. Instead, a message is displayed when the run completes specifying the number of errors found. (Note that the first 3/4 characters will still be stripped from these entries.)

Regards,
Brian.Kathy-Kelley-example-V3.xls
0
 
Martin LissOlder than dirtCommented:
The attached workbook contains a macro named 'Del' that does the job. The macro is assigned to Ctrl+z so if you want to do it again type press Ctrl+z.
Kathy-Kelley-example.xls
0
 
SteveCommented:
The following should do it:

Sub remove()

For x = 2 To Range("I65000").End(xlUp).Row

cellvalue = Trim(Cells(x, "I"))
cellvalue = Right(cellvalue, Len(cellvalue) - 3)
cellvalue = Left(cellvalue, Len(cellvalue) - 8)
Cells(x, "I") = Trim(cellvalue)

Next x

End Sub

Open in new window

Kathy-Kelley-example.xls
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
redmondbCommented:
Hi, esu4236.

Please attached. A couple of points...
(1) There are trailing blanks in the data so my macro strips them.
(2) The code assumes that each cell has "valid data". If not, then you will get an error message. Let me know if you would prefer that the macro to terminate cleanly with an error message or to quietly complete without an error.

The code is...
Sub Reformat_Data()
Dim xLast_Row As Long
Dim xCell     As Range
Dim xHold     As Variant

Sheets("Sheet1").Activate

xLast_Row = ActiveSheet.UsedRange.Cells(1, 1).Row + ActiveSheet.UsedRange.Rows.Count - 1
If xLast_Row < 2 Then
    MsgBox ("No data found in " & ActiveSheet.Name & " - run cancelled.")
    Exit Sub
End If

For Each xCell In Range("I2:I" & xLast_Row)
    xHold = xCell
    xHold = Trim(Mid(xHold, IIf(Mid(xHold, 4, 1) = " ", 5, 4), 9999))
    xHold = Mid(xHold, 1, Len(xHold) - 9)
    xCell = xHold
Next

End Sub

Open in new window

Regards,
Brian.
Kathy-Kelley-example-V2.xls
0
 
Martin LissOlder than dirtCommented:
In mine if you have a lot of data and you want to speed it up then change the macro to


Sub Del()
Dim lngLastRow As Long
Dim lngRow As Long
lngLastRow = Range("I65536").End(xlUp).Row

Application.ScreenUpdating = False

For lngRow = 2 To lngLastRow
    Cells(lngRow, 9).Value = Mid(Cells(lngRow, 9).Value, 4)
    If Left(Cells(lngRow, 9).Value, 1) = " " Then
        Cells(lngRow, 9).Value = Mid(Cells(lngRow, 9).Value, 2)
    End If
    Cells(lngRow, 9).Value = Mid(Cells(lngRow, 9).Value, 1, InStr(1, Cells(lngRow, 9), " ") - 1)
Next

Application.ScreenUpdating = True

End Sub

Open in new window

0
 
Martin LissOlder than dirtCommented:
Did any of the above help you?
0
 
esu4236Author Commented:
Yes it did!!!  Sorry I was out of the office on Friday.  

I am splitting the points between MartinLiss and Brian Redmond.  You were both VERY helpful, and it seems that the macro is working as it should.  Thank you so much for your prompt help on this.  And sorry I was not able to get back to you right away.  Hope you had a great weekend!!
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
 
redmondbCommented:
Thanks, esu4236.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.