Deleting characters to the right in a column....

[J.W. Aldridge]

I have a web query that pulls a string of data in which I only need
the first 8 characters.

Any way I can have a macro that will delete any characters to the
right of the 8th character in certain string/columns (multple columns
- A, G, & L)?

I will run this after the data is retrieved.

Thanx

 

[Zone]

How about this? James

Sub Left8()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If VarType(cell) = vbString Then
If cell.Column = 1 Or cell.Column = 7 Or cell.Column = 12 Then
If Len(cell) > 8 Then cell = Left(cell, 8)
End If
End If
Next cell
End Sub

 

[Guest]

Sub aldridge()
Dim r(3) As Range
Dim l(3) As Long

Set r(0) = Range("A1")
Set r(1) = Range("G1")
Set r(2) = Range("L1")

l(0) = Cells(Rows.Count, "A").End(xlUp).Row
l(1) = Cells(Rows.Count, "G").End(xlUp).Row
l(2) = Cells(Rows.Count, "L").End(xlUp).Row

For i = 0 To 2
For j = 0 To l(i)
v = r(i).Offset(j, 0).Value
v = Left(v, 8)
r(i).Offset(j, 0).Value = v
Next
Next
End Sub

 

[Gary Keramidas]

here's another approach:

Sub trimcells()
Dim i As Variant
Dim j As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Dim lastrow As Long

For Each i In Array("A", "G", "L")
lastrow = ws.Cells(Rows.Count, i).End(xlUp).Row
For j = 1 To lastrow
ws.Cells(j, i).Value = Left(ws.Cells(j, i).Value, 8)
Next
Next

End Sub

 

[Guest]

Your code is an improvement over mine. If we are REALLY careful about
defining the range, we can eliminate all the Ifs and Fors:

Sub aldridge()
Set r1 = Range("A:A,G:G,L:L")
Set r2 = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
Set r = Intersect(r1, r2)
r.Value = Left(r.Value, 8)
End Sub

 

[Zone]

I was concerned about what would happen if there were numbers and blank
cells in the ranges. Sure enough, if I throw in some numbers and blank
cells, I get Type Mismatch on the r.Value = line. James

 

[Guest]

Thanks James. It turns out you are 100% correct. We absolutely HAVE to
examine the individual items to avoid errors. The loop is necessary in this
case.

Thanks again

 

[Zone]

Thanks. I do like your intersect method, though!