Trunicate columns... Too slow! Faster way?

[Technowolf]

I'm using:

    Columns("AG:AG").Select    
Dim rCell As Range
    For Each rCell In ActiveSheet.UsedRange.Columns("AG").Cells
        rCell = Left(rCell, 9)
    Next rCell

To truncate my characters down. Problem is I've got like 11k rows and it takes forever. Is there a faster way to do the entire column?

 

[Norie]

Why not insert a blank column to the right of column AG, put this formula in the first row of the new column, copy down, then copy and paste special values from the new column to column AG and finally delete the column you inserted?

All of which could be done with code, if that's what you need.

 

[Technowolf]

It needs to be VBA, but that's not a terrible idea. Pretty much I need to shorten it to 9 characters, then format it to m/d/yyyy, all via macro, and without it taking forever to run.

 

[shg]

What's in those cells?

 

[Technowolf]

Date and time, but just needs to be date. The obvious solution would be to just format for date right? Well that makes it display properly, except I've got another complex formula elsewhere that if I just change the formatting to date it won't work, have to actually cut off the time.

 

[shg]

Sub x()
    Columns("AH").Insert
    With Intersect(Columns("AG"), ActiveSheet.UsedRange)
        .Offset(, 1).FormulaR1C1 = "=int(rc[-1])"
        .Value = .Offset(, 1).Value
        .NumberFormat = "m/d/yyyy"
        .Offset(, 1).EntireColumn.Delete
    End With
End Sub

 

[Technowolf]

Ah! Very clever shg! That worked absolutely perfectly. You're my hero!

 

[shg]

You're welcome.

 

[Rick Rothstein]

Here is another macro that you can consider which does not involve inserting/deleting a helper column...

Sub TruncateToNineCharacters()
  Dim Addr As String
  Addr = "AG1:AG" & Cells(Rows.Count, "AG").End(xlUp).Row
  Range(Addr) = Evaluate("IF(ROW(),INT(" & Addr & "))")
  Range(Addr).NumberFormat = "m/d/yyyy"
End Sub