Slight change to Macro to expand Cell

EE Pros,

I have a Macro that EE helped me build.  What it does is expand the Wrap Text capability so you can type in line after line of text and then it will auto. expand the cell.  Very cool!

Here's my question;  when I copy a cell, outside the column that is affected by this macro, it auto copies the Wrap Text attribute.  Is there a way to limit it to a specific Column?



Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
For Each c In Target.Cells
        Application.ScreenUpdating = False
c.WrapText = True

        If c.WrapText Then c.Rows.AutoFit
        Next
        Application.ScreenUpdating = True
End Sub
Bright01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Let's Say you want to do this for A Column only..then you can do this code..This will wrap text only when you write in A Column...

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
Dim c As Range
For Each c In Target.Cells
        Application.ScreenUpdating = False
c.WrapText = True

        If c.WrapText Then c.Rows.AutoFit
        Next
        Application.ScreenUpdating = True
End Sub

Open in new window


Saurabh...
0
Bright01Author Commented:
Thank you!  I think I get it.  One last question..... when I add "insert copied cells" as I expand my model, it takes a minute or two.  I believe that's because, although I've limited the macro to a single column, it is going through the entire spreadsheet as it adds the copied cells.  If I want to speed up the process, can I simply limit the range?  In your example above, it would be something like modifying the range to A6:A500 instead of A:A....... is that right?

Thanks again,

B.
0
ProfessorJimJamCommented:
bright01

yes,  limit your range and here is the modified code limited to the specific range.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A6:A500")) Is Nothing Then Exit Sub
Dim c As Range
For Each c In Target.Cells
        Application.ScreenUpdating = False
c.WrapText = True

        If c.WrapText Then c.Rows.AutoFit
        Next
        Application.ScreenUpdating = True
End Sub

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Saurabh Singh TeotiaCommented:
Like Jim said..you need to change this line..

If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub

To this..

If Intersect(Target, Me.Range("A6:A500")) Is Nothing Then Exit Sub
0
Bright01Author Commented:
Jim and Saurabh,

I tried this.  It didn't work.  Let me describe the problem to you;

First, if I turn off this macro, I can cut and paste rows easily.

If I then Copy a row and insert Copied Cells, it hangs for 1 to 2 minutes.  Then the Row is inserted.

help!

B.
0
Bright01Author Commented:
"If I then turn the Macro back on, and Copy a row and insert Copied Cells, it hangs for 1 to 2 minutes.  Then the Row is inserted."

Sorry.

B.
0
Saurabh Singh TeotiaCommented:
Ahhh i see your problem.. Use this version of code...

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A6:A500")) Is Nothing Then Exit Sub
Dim c As Range, r As Range
Set Rng = Range("A" & Target.Row & ":A" & Target.Row)
For Each c In Rng
        Application.ScreenUpdating = False
c.WrapText = True

        If c.WrapText Then c.Rows.AutoFit
        Next
        Application.ScreenUpdating = True
End Sub

Open in new window


Saurabh...
0
Bright01Author Commented:
Saurabh,

I think we almost have it.  I'm still getting an error "rng is "variable not defined"..........

B.
0
Saurabh Singh TeotiaCommented:
Change this line...

Dim c As Range, r As Range

To this...

Dim c As Range, rng As Range

Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Saurabh and Jim,

Thank you for trouble shooting this.   In the end, Saurabh's guidance was "on the money" for fixing the issue.

Thanks again,

B.
0
ProfessorJimJamCommented:
thanks Bright01 for the feedback.

glad Saurabh's solution worked for you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.