Macro needed as soon as possible [Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,
i m smith i need a macro to split the data in every 5th cell .I am giving you the data below as an example

1.
2.
3.Darlene Thornton
4.197 Moody St
5.Baxley GA 31513-0432
6.912-367-1945
7.
8.Darlene Thornton
9.1205 George St
10.Waycross GA 31503-4916
11.912-283-0000

so from the above data given i need to split the row which is divisible by 5 into columns delimited by space . can anybody help me with a macro to my e-mail address as soon as possible.

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
Try this


Sub splitMe()

Dim lastRow As Long
Dim myRow As Long

lastRow = Cells(65536, 1).End(xlUp).Row

For myRow = 5 To lastRow Step 5
Cells(myRow, 1).Select

Selection.TextToColumns DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False, _
TrailingMinusNumbers:=True

Next



End Sub


Feed back will be good.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!