srrboarder
New Member
- Joined
- Nov 29, 2010
- Messages
- 8
I have a macro that is no longer working. The odd thing is, everything about the report (except the raw data) and the macro has not changed since its original creation. I'm assuming that an update either in excel or our database system is causing the macro to not perform properly. Unfortunately, I am not familiar enough with macros to be able to alter/re-program it to again function properly.
I need the macro to run through the list of addresses in column B (Address 1) and find multiple entries in the Address. When duplicates are found, I need it to combine the entries, provide a sum for the address, and then color any line with 1+ entries yellow. Once the entries are combined, I need to be able to "expand" the yellow rows to view all entries that were at the same address.
The code I have now is:
This is performing improperly because it only creates one expandable row AND it's looking at the wrong row (items in column A before the macro runs). I've tried changing everything I can think of and no matter what I do it does not operate as intended.
If you have any questions for me, please let me know. Also, I would prefer a macro solution for this. I know a Pivot table and conditional formatting would technically work for this, but due to complications between excel programs at my company, pivot tables can be troublesome so a macro is likely the best solution here (unless you have a non pivot table suggestion).
Thanks to anyone who may be able to help!
I need the macro to run through the list of addresses in column B (Address 1) and find multiple entries in the Address. When duplicates are found, I need it to combine the entries, provide a sum for the address, and then color any line with 1+ entries yellow. Once the entries are combined, I need to be able to "expand" the yellow rows to view all entries that were at the same address.
The code I have now is:
Code:
Sub duplicatecasemacro()
'
' duplicatecasemacro Macro
'
' Keyboard Shortcut: Ctrl+z
'
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range("A1").Select
Selection.End(xlToRight).Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(1), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("B9064").Select
Selection.End(xlDown).Select
Range("B16536").Select
Range(Selection, Selection.End(xlUp)).Select
Range("B1189:B16536").Select
Range("B16536").Activate
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="1"
Selection.FormatConditions(1).Interior.ColorIndex = 6
Range("B16536").Select
Selection.End(xlUp).Select
Range("B1189").Select
End Sub
This is performing improperly because it only creates one expandable row AND it's looking at the wrong row (items in column A before the macro runs). I've tried changing everything I can think of and no matter what I do it does not operate as intended.
If you have any questions for me, please let me know. Also, I would prefer a macro solution for this. I know a Pivot table and conditional formatting would technically work for this, but due to complications between excel programs at my company, pivot tables can be troublesome so a macro is likely the best solution here (unless you have a non pivot table suggestion).
Thanks to anyone who may be able to help!