Forum Discussion
Concatenating column B values if left adjacent cell is empty
I revised the macro to allow up to 200 more rows of results in case of overflow, as well as up to 165 characters in next pass through the concatenation loop. When an overflow situation is detected, the server name will be repeated as often as needed to get all software names listed on additional rows.
Sub Sequelize() Dim rg As Range Dim delimiter As String, s As String Dim i As Long, k As Long, n As Long, nData As Long Dim vData As Variant, vResults As Variant delimiter = ", " Set rg = Range("A2").CurrentRegion Set rg = rg.Offset(1, 0).Resize(rg.Rows.Count - 1, rg.Columns.Count) n = rg.Rows.Count nData = Application.CountA(rg.Columns(1)) nData = nData + 200 'Allow for servers whose software list exceeds 32,767 characters vData = rg.Value ReDim vResults(1 To nData, 1 To 2) For i = 1 To n If vData(i, 1) <> "" Then k = k + 1 vResults(k, 1) = vData(i, 1) If s <> "" Then If i > 1 Then vResults(k - 1, 2) = Left$(s, 32767) End If s = IIf(vData(i, 2) = "", "", vData(i, 2)) Else If vData(i, 2) <> "" Then If s = "" Then s = vData(i, 2) Else s = s & delimiter & vData(i, 2) End If End If End If 'Overflow occurs if you put more than 32,767 characters in a cell. This block allows up to 165 characters (plus delimiter) in next pass through loop. If Len(s) > 32600 Then If (i < n) And (vData(i + 1, 1) = "") Then vResults(k, 2) = s vResults(k + 1, 1) = vResults(k, 1) s = "" k = k + 1 End If End If Next If s <> "" Then vResults(k, 2) = Left$(s, 32767) rg.ClearContents rg.Resize(nData, 2).Value = vResults End Sub
Here is a couple screenshots of the macro with a before and after running.
I would like test my code on your actual workbook. It's OK if you replace all the data with the letter "x" as long as you can reproduce the problem. You can post a workbook in this thread, or you could email it to me at first initial last name at my ISP, which is alum dot mit dot edu. I would then like to try reproducing the problem. Fixing the problem is easy if I can reproduce it.
It's possible that there is a limitation of Excel VBA present in your version of Excel that is not in mine. All my testing so far has been on 64-bit Excel 2016/Office 365 running on Windows 10--but I have other versions available for testing (both Mac and Windows, 32-bit and 64-bit, 2007 to date).
Please tell me:
Which version of Excel do you use, Mac or Windows?
If Windows, is 2007, 2010, 2013, 2016 or 2019?
Is it 32-bit or 64-bit?
- "Subscript out of range" errors are commonly caused by code that references a range or worksheet by a specific name--and that nam e doesn't exist in your actual workbook. For example, code is looking for Worksheets("Sheet1") and you have one named "Sheet 1" with a space.
It really isn't possible to debug further without seeing a workbook that reproduces the error. I would then use the debugger to see which statement generates the error and what it is looking for. - Getting runtime error '9'.
Subscript out of range Yeah, unfortunately I am working with such a large amount of data here that it doesn't want to cooperate by making it easy and convenient to read. There are thousands of servers and the software includes everything from Google Chrome to .net framework updates to security updates for various tools. But what I have now is the easiest to search through I believe. I can query the inventory for a server and then it will just show me the column(s) of data associated with it. From there I can just Ctrl+F to search more specifically. Thanks for trying to help me with my issue. I appreciate it. I have much to learn!!
I see, thank you for the explanation. Perhaps servers in columns will be more compact and searchable, but it all depends on your concrete data.
I am importing this into a sharepoint webpage through the excel app and if I kept all the applications in columns then some of the servers would have 100+ columns. I am positive there is a better way to accomplish my task but am a new intern and I lack the knowledge to do so. :/
If not a secret, what's the reason keep list of server software in so long strings instead of columns cell?
You have done it! Thank you for your dedication to this issue of mine. This works perfectly. Brad_Yundt
I revised the macro to allow up to 200 more rows of results in case of overflow, as well as up to 165 characters in next pass through the concatenation loop. When an overflow situation is detected, the server name will be repeated as often as needed to get all software names listed on additional rows.
Sub Sequelize() Dim rg As Range Dim delimiter As String, s As String Dim i As Long, k As Long, n As Long, nData As Long Dim vData As Variant, vResults As Variant delimiter = ", " Set rg = Range("A2").CurrentRegion Set rg = rg.Offset(1, 0).Resize(rg.Rows.Count - 1, rg.Columns.Count) n = rg.Rows.Count nData = Application.CountA(rg.Columns(1)) nData = nData + 200 'Allow for servers whose software list exceeds 32,767 characters vData = rg.Value ReDim vResults(1 To nData, 1 To 2) For i = 1 To n If vData(i, 1) <> "" Then k = k + 1 vResults(k, 1) = vData(i, 1) If s <> "" Then If i > 1 Then vResults(k - 1, 2) = Left$(s, 32767) End If s = IIf(vData(i, 2) = "", "", vData(i, 2)) Else If vData(i, 2) <> "" Then If s = "" Then s = vData(i, 2) Else s = s & delimiter & vData(i, 2) End If End If End If 'Overflow occurs if you put more than 32,767 characters in a cell. This block allows up to 165 characters (plus delimiter) in next pass through loop. If Len(s) > 32600 Then If (i < n) And (vData(i + 1, 1) = "") Then vResults(k, 2) = s vResults(k + 1, 1) = vResults(k, 1) s = "" k = k + 1 End If End If Next If s <> "" Then vResults(k, 2) = Left$(s, 32767) rg.ClearContents rg.Resize(nData, 2).Value = vResults End SubIt sounds like it will work. I will be able to try it in a couple hours or so and will report back.
As far as the overflow, additional rows are acceptable.Brad_Yundt
Thanks for sending me the file. I can reproduce the problem with my Excel 2016/Office 365 64-bit.
Excel cells can hold a maximum of 32,767 characters. Your problem server has 86,510 characters worth of software. I am guessing an overflow occurs that results in the loss of your data.
To test this hypothesis, I modified the code to truncate the software list to the first 32,767 characters. Now the macro seems to work.
Sub Sequelize() Dim rg As Range Dim delimiter As String, s As String Dim i As Long, k As Long, n As Long, nData As Long Dim vData As Variant, vResults As Variant delimiter = ", " Set rg = Range("A2").CurrentRegion Set rg = rg.Offset(1, 0).Resize(rg.Rows.Count - 1, rg.Columns.Count) n = rg.Rows.Count nData = Application.CountA(rg.Columns(1)) vData = rg.Value ReDim vResults(1 To nData, 1 To 2) For i = 1 To n If vData(i, 1) <> "" Then k = k + 1 vResults(k, 1) = vData(i, 1) If s <> "" Then If i > 1 Then vResults(k - 1, 2) = Left$(s, 32767) End If s = IIf(vData(i, 2) = "", "", vData(i, 2)) Else If vData(i, 2) <> "" Then If s = "" Then s = vData(i, 2) Else s = s & delimiter & vData(i, 2) End If End If End If Next If s <> "" Then vResults(k, 2) = Left$(s, 32767) rg.ClearContents rg.Resize(nData, 2).Value = vResults End SubPlease try running the revised macro on your full dataset. If it works, we can then shift discussion to how best to handle the text in the overflow. Perhaps additional columns or rows?
