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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
[RESOLVED] [Excel] split rows down macro
Reply With Quote
