[RESOLVED] [Excel] split rows down macro

Hi All

am new to VBA but have been using vb6 for the past few months
I am trying to modify the code in this thread to work on my spread sheet.

the sample spreadsheet that came with the code from that thread worked ok with the code.
Thing is if a cell has only one value in it the macro stops seperating out any further multiple value rows (on the sample sheet)

on my sheet I have accounted for this by testing the boundaries of the split array, but another problem then is it seems to only do the first row then stops splitting them altogether, although it does step down through the rest of the rows

Ultimatly I want to be able to customize this so it works by selected column on a current work sheet, instead of the column being coded.

a sample of my spreadsheet is attached including the current code I am working with.

the only cell in your sheet that meets the split criterior is in row 1
C1 is the only cell that has a comma followed by a space

Oh my god i am so asleep this morning. Thanks westconn.

as i said I'm new to vba so would you have any suggestions on how to turn this from using column C1 to any selected col?

was thinking something like this:

vb Code:

Set rngSource = ThisWorkbook.Worksheets(1).Range((ActiveCell.Column) & "1")

but it gives an error so I think I've got the wrong idea :rolleyes:

vb Code:

Set rngSource = ActiveCell.EntireColumn

this works

ah dont think I explained myself well, sorry am only learning this as i go along

The code you gave selects the entire column of the column the active cell is in, yes?

I need it that the first cell of the column of the column of the active cell is selected.

ie
If I select C5 as the active cell, then run the macro rngSource will then be given C1; or if I select D19 then D1 will be given to rngSource;
thus having the same effect as hardcoding

vb Code:

Set rngSource = ThisWorkbook.Worksheets(1).Range("C1")

so I need to get the column label and then add "1" (the row) to it to be a starting point for the macro.
overall this means the user can select the column for the macro to act on by just selecting any cell in that column.

Ta

Set rngSource = Cells(1, ActiveCell.Column)

have it :D

vb Code:

Set rngSource = Cells(1, (ActiveCell.Column))

does the job. thanks for all your help westconn1 :wave:

edit: hey posted at the same time; you know what they say about great minds!