How do I set up macro to truncate column/field?
 

I have been asked to write a macro in visual basic to truncate a specific
column in excel not to exceed 25 characters.

How do I set up macro to truncate column/field?
 

Hi,

Try:

'=============
Public Sub Tester()
Dim rng As Range
Dim rCell As Range
Const col As String = "A" '<<==== CHANGE

Set rng = Intersect(ActiveSheet.UsedRange, _
Columns(col))

For Each rCell In rng.Cells
With rCell
If Len(.Value) 25 Then
.Value = Left(.Value, 25)
End If
End With
Next rCell

End Sub
'<<=============

---
Regards,
Norman


"Macro to truncate column" <Macro to truncate
wrote in message
...
I have been asked to write a macro in visual basic to truncate a specific
column in excel not to exceed 25 characters.

How do I set up macro to truncate column/field?
 

Thanks so much.

So I go to tools, macro, VB Editor and just put this code in the right pane
and than change (where you show "change") the title of the column, or the
letter (i.e., "D")?

Thanks for your help.

Linda

"Norman Jones" wrote:

Hi,

Try:

'=============
Public Sub Tester()
Dim rng As Range
Dim rCell As Range
Const col As String = "A" '<<==== CHANGE

Set rng = Intersect(ActiveSheet.UsedRange, _
Columns(col))

For Each rCell In rng.Cells
With rCell
If Len(.Value) 25 Then
.Value = Left(.Value, 25)
End If
End With
Next rCell

End Sub
'<<=============

---
Regards,
Norman


"Macro to truncate column" <Macro to truncate
wrote in message
...
I have been asked to write a macro in visual basic to truncate a specific
column in excel not to exceed 25 characters.

How do I set up macro to truncate column/field?
 

Hi Linda,

Alt-F11 to open the VBE
Insert | Module (if no module already exists)
Paste the code
With the cursor anywhere in the pasted code, F5 to run the macro
Alt-F11 to return to Excel

Const col As String = "A" '<<==== CHANGE

Change 'A' to the column of interest - perhaps 'D'

You may wish to visit David McRitchie's 'Getting Started With Macros And
User Defined Functions' at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


---
Regards,
Norman


"Macro to truncate column"
wrote in message ...
Thanks so much.

So I go to tools, macro, VB Editor and just put this code in the right
pane
and than change (where you show "change") the title of the column, or the
letter (i.e., "D")?

Thanks for your help.

Linda

"Norman Jones" wrote:

Hi,

Try:

'=============
Public Sub Tester()
Dim rng As Range
Dim rCell As Range
Const col As String = "A" '<<==== CHANGE

Set rng = Intersect(ActiveSheet.UsedRange, _
Columns(col))

For Each rCell In rng.Cells
With rCell
If Len(.Value) 25 Then
.Value = Left(.Value, 25)
End If
End With
Next rCell

End Sub
'<<=============

---
Regards,
Norman


"Macro to truncate column" <Macro to truncate
wrote in message
...
I have been asked to write a macro in visual basic to truncate a
specific
column in excel not to exceed 25 characters.

How do I set up macro to truncate column/field?
 

Norman,

I get an error message.

1. The Public Sub Tester () shows up in yellow

Error message:

Compile error
Constant expession required (for COL)

Linda

"Macro to truncate column" wrote:

I have been asked to write a macro in visual basic to truncate a specific
column in excel not to exceed 25 characters.