Need help creating VBA to remove spaces, replace it with a new line in cell (Alt + Enter)
I need vba code to find any spaces greater than 1, remove those spaces and replace that empty space with a new line (Alt + Enter). Then move down to the next cell in the selection and repeat.
1: Each line cannot be more than 70 characters per cell. (more of a fail safe as most of the time there won't be more than 70 characters before the spaces)
2: I need to be able to select the the cells that I want this to apply to. ( The selection will always be 1 column only)
3: It will only apply to visible rows. (Not applying to hidden rows by filter)
Below is an example of the starting data, and what the desired results should be.
Re: Need help creating VBA to remove spaces, replace it with a new line in cell (Alt + Ent
The first macro posted almost worked, but it only worked on the first Cell (A2). It took those results and copied them all the way down. It needs to adjust cell A2, then move down and adjust Cell A3, ect... until the last cell with data in it.
The second macro worked perfectly, except it needs to overwrite the existing data, instead of putting it 2 columns over.
Last edited by Elreezy; 10-26-2022 at 12:17 PM.
Reason: forgot to include what code i was referring to
According to your attachment a VBA demonstration as a beginner starter :
PHP Code:
Sub Demo1() Const D = "¤" Dim Rg As Range, F%, S, C& Application.ScreenUpdating = False For Each Rg In Selection If Not Rg.Rows(1).Hidden Then F = 0 S = Split(Rg) For C = 0 To UBound(S) - 1 If S(C) = "" And S(C + 1) = "" Then S(C) = vbLf: F = 1 While S(C + 1) = "": C = C + 1: S(C) = D: Wend End If Next If F Then Rg = Join(Filter(S, D, False)) End If Next Application.ScreenUpdating = True End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Last edited by Marc L; 10-26-2022 at 02:19 PM.
Reason: optimization ...
This works perfectly. Now I just need to have my starting point at J2 instead of A1. I'm somewhat of a novice but usually pretty good at manipulating existing code to my needs. However, I am sturggling to get this to work in Column J (with J2 being the starting point as i have headers).
Re: Need help creating VBA to remove spaces, replace it with a new line in cell (Alt + Ent
My apologies I should have done that initially. I over estimated my ability to manipulate code.
As in my original post, I was wanting to be able to highlight with a selection the data I will be manipulating. Example: Select J2:J611, then run this macro.
The reason for this is because sometimes i may need to make similar adjustments in columns K. Its imperative it does not effect any existing data outside of the selection.
Re: Need help creating VBA to remove spaces, replace it with a new line in cell (Alt + Ent
With these new specs (title in J1 and data from J2 and down), then it's simpler. Look at:
PHP Code:
Sub Example4() Dim s, a, Q&, i&, Tmp Application.ScreenUpdating = False a = Range("J1", [J1].End(xlDown)).Value: Q = UBound(a): s = ChrW(9742) For i = 2 To Q Tmp = Split(Replace(a(i, 1), " ", s & "@" & s), "@") Tmp = Filter(Tmp, s & s, False): Tmp = Join(Tmp, vbLf) a(i, 1) = Replace(Replace(Tmp, s & " ", ""), s, "") Next [J1].Resize(Q) = a: [J1].Resize(Q).Rows.AutoFit End Sub
LinkBack URL
About LinkBacks
Register To Reply
Try this !
Bookmarks