Hi Bhatti ,
I am going to have to step aside and let another developer pick up the task. I’m not comfortable with being paid by anything other than PayPal so I’m out.
Sorry I can’t help you this time
Posts by trunten
-
-
I can certainly look at this for you. Could you confirm you have paid 10% to ozgrid?
-
Sorry, didn’t check in the past couple of days but I am quite happy for KjBox to sort you out 😀
-
I'm not really sure what you are asking for. Could you elaborate a bit more and maybe provide some example outputs?
-
See if this works for you
Display MoreCode- Sub Macro1()
- Dim d, a, k, i&, s$
- a = ActiveSheet.UsedRange.Value
- If Not IsArray(a) Then Exit Sub
- Set d = CreateObject("Scripting.Dictionary")
- For i = 1 To UBound(a)
- If d.exists(a(i, 1)) Then
- s = d(a(i, 1)) & vbLf & a(i, 3)
- Else
- s = a(i, 3)
- End If
- d(a(i, 1)) = s
- Next i
- If d.Count > 0 Then
- i = 0
- ReDim a(1 To d.Count, 1 To 2)
- For Each k In d.keys
- i = i + 1
- a(i, 1) = k
- a(i, 2) = d(k)
- Next k
- With Workbooks.Add.Sheets(1).Range("A1").Resize(UBound(a), UBound(a, 2))
- .Value = a
- .EntireColumn.AutoFit
- .EntireRow.AutoFit
- End With
- End If
- Set d = Nothing
- Erase a
- End Sub
-
Does this work for you?
-
I can take a look for you. I’ll let you know if I have any questions
-
Payment received. Many thanks
-
I think I’m done. PM’d you a file
-
I can look at this for you.
-
I have your solution ready for you. I've PM'd you my payment details
-
I can look at this for you
Could you confirm you have paid the 10% to ozgrid?
-
Don’t know how I’ve avoided it for so long then. It would even help if it was consistent. I had code that was working fine for ages, then I hid a completely unrelated column and things just started breaking! Very confusing 😂
-
First off, appologies if this isn't the right forum for this but I wasn't sure where would be most appropriate.
Now, onto the strange behaviour of SpecialCells(xlCellTypeVisible). Not sure if this is specific to my version of excel (2016) but if you query SpecialCells(xlCellTypeVisible) on a single cell with now rows or columns anywhere on the sheet it will return just the one cell. However, if there is a column or row anywhere on the sheet is hidden then it gets very weird. For example if I run the following code:
I get the the following results:
No rows/columns hidden: $A$1
Column D hidden: $A:$C,$E:$XFD
Row 12 hidden: $1:$11,$13:$1048576
Row 12 & Column D hidden: $A$1:$C$11,$E$1:$XFD$11,$A$13:$C$1048576,$E$13:$XFD$1048576
Pretty sure nobody would expect that behaviour so if it's more wide spread than just excel 2016 it's probably something that you need to take into account!
Attached a workbook just to show what I mean.
-
No problem. glad I could help.
-
-
One question though. Could you not use SUMIFS?
-
might be able to avoid multiple loops with a bit of a hack like so:
Display MoreCode- Sub summarise()
- Dim dataSheet As Worksheet
- Dim summaryTable As Range
- Dim data, summary, d
- Dim i As Long, x As Long
- Dim k As String
- Set dataSheet = Sheets("Data")
- Set summaryTable = Sheets("Summary Table").Range("A1:C7")
- Set d = CreateObject("Scripting.Dictionary")
- summary = summaryTable.Value
- data = dataSheet.Range("A1").CurrentRegion.Value
- For i = 2 To UBound(summary)
- d(summary(i, 1) & "|" & summary(1, 1) & "|" & summary(i, 2)) = i
- summary(i, 3) = 0#
- Next i
- For i = 2 To UBound(data)
- k = data(i, 1) & "|" & data(i, 3) & "|" & data(i, 8)
- If d.exists(k) Then
- x = d(k)
- summary(x, 3) = summary(x, 3) + data(i, 7)
- End If
- Next i
- summaryTable.Value = summary
- Erase data
- Erase summary
- Set dataSheet = Nothing
- Set summaryTable = Nothing
- Set d = Nothing
- End Sub
I've also attached an example so you can see how it may work
-
Payment received. Many thanks
-
No problem. Glad I could help.
PM’d you my payment details