Splitting Columns in Excel
443
post-template-default,single,single-post,postid-443,single-format-standard,bridge-core-3.1.6,qi-blocks-1.3.1,qodef-gutenberg--no-touch,qodef-qi--no-touch,qi-addons-for-elementor-1.7.6,qode-page-transition-enabled,ajax_fade,page_not_loaded,,qode_grid_1300,qode-content-sidebar-responsive,qode-child-theme-ver-1.0.0,qode-theme-ver-30.4.1,qode-theme-bridge,disabled_footer_top,wpb-js-composer js-comp-ver-7.5,vc_responsive,elementor-default,elementor-kit-1696

Splitting Columns in Excel

If you’re like me and get frustrated with Excel’s lack of column splitting, run this macro to split them.

Sub SingleToMultiColumn()
Dim rng As Range
Dim iCols As Integer
Dim lRows As Long
Dim iCol As Integer
Dim lRow As Long
Dim lRowSource As Long
Dim x As Long
Dim wks As Worksheet

Set rng = Application.InputBox _
(prompt:="Select the range to convert", _
Type:=8)
iCols = InputBox("How many columns do you want?")
lRowSource = rng.Rows.Count
lRows = lRowSource / iCols
If lRows * iCols <> lRowSource Then lRows = lRows + 1

Set wks = Worksheets.Add
lRow = 1
x = 1
For iCol = 1 To iCols
Do While x <= lRows And lRow <= lRowSource Cells(x, iCol) = rng.Cells(lRow, 1) x = x + 1 lRow = lRow + 1 Loop x = 1 Next End Sub

Nicole
[email protected]

Share a little biographical information to fill out your profile. This may be shown publicly.