cells(Row,Column-1)=.cells(Row,Column-1).Value2 _ In VBA (psuedo code) For (Columns, 2, LastColumn, Step 2) Here are two ways forward without merged cells. Merged cells are absolutely horrid and considered among the greatest of Excel sins. That merged cell can cause those lines to either halt with an error or continue with the possibility of unwanted consequences depending on which cell addresses actually hold row 1 data. Care to guess why? The merged cell you currently have in row 1. Each one of those lines needs to be handled uniquely. So i urge you to reconsider using merged cells.Īlmost to prove the point, you'll find "*****" on a few lines in the two solutions below. They delay development and add complexity to formulas and VBA code all the while creating more opportunities for an error to occur or a bug to go unnoticed. They interrupt the double click autofill and make copying and pasting an exercise in frustration. They play havoc when dragging an area to populate cells. M圜ell = Right(Merged, Len(Merged) - 1) 'Insert String Merged = Merged & Chr(32) & M圜ell.Offset(, i - 1) 'Build String replacing M圜ell.Resize(1, i -1).Merge with M圜ell.Resize(1, MaxCol).Merge where MaxCol is your max used column.ĭim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")ĭim M圜ell As Range, Merged As String, i, iArrįor Each M圜ell In ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)įor i = 1 To ws.Cells(M圜ell.Row, ws.Columns.Count).End(xlToLeft).Column If you don’t want that to be the case, you will need to find the max used column and merge by that column index Therefore, some merged cells will span 2 columns and some will span 3. Each merge size is dependent on each rows furthest right non-blank column. This is dynamic by row ( determined by Column A) and column. I never recommend merging cells, but if you must. If it helps, before on left, after on right: How do I get this to loop properly through each row? 'Looping through A column define max valueĬell_value = (1, i).value OutputText = outputText & cell.value & delimĪnd here's what I've got as far as trying to get it to look through each row. 'and puts the resulting text in top most cell Here is what I have so far: Sub JoinAndMerge() I managed to get some code together that will combine the cells properly, but I'm struggling to use this code to look through each row and combine the cells in that row, for all rows that contain data. I need each row to combine the data in the respective row without losing any of the data from any columns. I'm getting data that is in 1, 2, or 3 columns (possibly more).
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |