d2jsp
Log InRegister
d2jsp Forums > Off-Topic > Computers & IT > Programming & Development > Macro In Excel Question
12Next
Add Reply New Topic New Poll
Trade Moderator
Posts: 26,725
Joined: Dec 20 2005
Gold: 82,500.00
Trader: Trusted
Aug 26 2020 08:09am
So I have this Macro
Code
Sub combineRows()
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) = s
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) = s

rg.ClearContents
rg.Resize(nData, 2).Value = vResults
End Sub


the in theory should run until the end of the file ( last row of column A ) and concatenate the values from B column to just 1 row.
The problem is that the macro stops without any error..
Any VBA experts here that can chip in?


also, the excel file itself have more then 140K rows so maybe that the problem I don't know :(
Member
Posts: 71,555
Joined: Aug 14 2007
Gold: 114,236.06
Warn: 10%
Oct 4 2020 01:43am


Code
Set rg = Range("A2").CurrentRegion


is most likely your issue if your expected range contains a blank value.
PM if you still need help with this - will need a copy/example of your source as well
Trade Moderator
Posts: 26,725
Joined: Dec 20 2005
Gold: 82,500.00
Trader: Trusted
Oct 4 2020 01:51am
Quote (Penguins0690 @ Oct 4 2020 10:43am)
^yakir

Code
Set rg = Range("A2").CurrentRegion


is most likely your issue if your expected range contains a blank value.
PM if you still need help with this - will need a copy/example of your source as well


oh I got it now.. it won't treat empty cell as something he need to work with.. gotcha.

and you mean source file or code? cuz the above is the code I use.
I will give you a background :

I got a customer that gave me an excel file that have plenty of empty and/or cell with values that for example all connected to 1 row ( meaning in Column A row 2 I have ID 123 and until the next ID I need to combine all B column values to 1 row and then delete the others ).
Member
Posts: 71,555
Joined: Aug 14 2007
Gold: 114,236.06
Warn: 10%
Oct 4 2020 01:58am
Quote (Yakir @ Oct 4 2020 02:51am)
oh I got it now.. it won't treat empty cell as something he need to work with.. gotcha.

and you mean source file or code? cuz the above is the code I use.
I will give you a background :

I got a customer that gave me an excel file that have plenty of empty and/or cell with values that for example all connected to 1 row ( meaning in Column A row 2 I have ID 123 and until the next ID I need to combine all B column values to 1 row and then delete the others ).


i meant source, you posted the code you had

but if it's fixed i guess you're all good then :)
Trade Moderator
Posts: 26,725
Joined: Dec 20 2005
Gold: 82,500.00
Trader: Trusted
Oct 4 2020 02:00am
Quote (Penguins0690 @ Oct 4 2020 10:58am)
i meant source, you posted the code you had

but if it's fixed i guess you're all good then :)


by "I got it now" I meant that I understand what you were saying :D
I still need help with the above.
Member
Posts: 71,555
Joined: Aug 14 2007
Gold: 114,236.06
Warn: 10%
Oct 4 2020 02:03am
Quote (Yakir @ Oct 4 2020 03:00am)
by "I got it now" I meant that I understand what you were saying :D
I still need help with the above.


can you dm me a snippet of the source?

This post was edited by Penguins0690 on Oct 4 2020 02:04am
Member
Posts: 71,555
Joined: Aug 14 2007
Gold: 114,236.06
Warn: 10%
Oct 4 2020 02:39am
Code
Sub a_b_merge()
lastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
delimiter = ", "

For i = 1 To lastRow
Cells(i, 3) = Cells(i, 1) & delimiter & Cells(i, 2)
Next i
Columns(1).EntireColumn.Delete
Columns(1).EntireColumn.Delete

lastRow2 = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

For j = 1 To lastRow2
If Cells(j, 1) = "" Then
Rows(j).EntireRow.Delete
lastRow2 = lastRow2 - 1
j = j - 1
End If
If j = lastRow2 Then
Exit For
End If
Next j
End Sub


pretty sure i understood what you were going for, lmk if not

if Column A is nothing, skip the combine
else combine A + , + B in Column C

remove column A + B, then remove blank rows from Column C (now column A)

This post was edited by Penguins0690 on Oct 4 2020 03:02am
Trade Moderator
Posts: 26,725
Joined: Dec 20 2005
Gold: 82,500.00
Trader: Trusted
Oct 4 2020 02:54am
even if its empty combine.
Member
Posts: 71,555
Joined: Aug 14 2007
Gold: 114,236.06
Warn: 10%
Oct 4 2020 03:03am
Quote (Yakir @ Oct 4 2020 03:54am)
even if its empty combine.


changed, do you want it to delete fully blank rows after the combine as well? (already included in the j loop)
Trade Moderator
Posts: 26,725
Joined: Dec 20 2005
Gold: 82,500.00
Trader: Trusted
Oct 4 2020 03:03am
Quote (Penguins0690 @ Oct 4 2020 12:03pm)
changed, do you want it to delete fully blank rows after the combine as well? (already included in the j loop)


Yes.
I will test it out and see the outcome :) thanks.
Go Back To Programming & Development Topic List
12Next
Add Reply New Topic New Poll