Drill down using VBA

Guinaba

Board Regular
Joined
Sep 19, 2018
Messages
165
Office Version
  1. 2016
Platform
  1. Windows
Hi guys,

Just wondering if someone could help me out, I have the following VBA code allowing the user to click in the active cell "+" and expand by the number of items within that brand. .

VBA Code:
Sub Hide_SKU()
With Sheet1
    ActRow = ActiveCell.Row
    LastItemRow = ActiveCell.Offset(1, -1).End(xlDown).Row - 1
    ActiveCell.Value = "+"
    .Range(ActRow + 1 & ":" & LastItemRow).EntireRow.Delete
End With
End Sub

Img1.JPG

Img2.JPG


However, when I click again on ("-") I am getting the following error:

Run-time error "1004":
This won't work becasue it would move cells in a table on your worksheet

Here is the full VBA code if helps:

VBA Code:
Option Explicit
Dim BrandIndex, Item As String
Dim LastItemRow, LastFiltRow, ActRow, ItemQty As Long

Sub Show_SKU()
With Sheet1
    ActRow = ActiveCell.Row
    BrandIndex = .Range("S" & ActRow).Value 'Get BrandIndex name
    LastItemRow = Sheet2.Range("c99999").End(xlUp).Row 'Get last row of items
    Sheet2.Range("v2,v5:ak99999").ClearContents 'Clear any previous data
    Sheet2.Range("v2").Value = BrandIndex
    Sheet2.Range("a1:p" & LastItemRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheet2.Range("v1:Ak2"), CopyToRange:=Sheet2.Range("v4:AK4"), Unique:=True
    LastFiltRow = Sheet2.Range("v99999").End(xlUp).Row  'Last FilterRow
    If LastFiltRow < 5 Then GoTo NoInv
    ItemQty = LastFiltRow - 4 'Get The Number of items
    ActiveCell.Value = "-"
    ActiveCell.EntireRow.Offset(1).Resize(ItemQty + 1).Insert Shift:=xlDown 'Inserts # of Rows + 1 for Header
    .Range("E" & ActRow + 1 & ":E" & ActRow + 1).HorizontalAlignment = xlLeft 'Justify Left item name
    .Range("E" & ActRow + 1 & ":R" & ActRow + 1).HorizontalAlignment = xlCenter 'Justify Center item numbers
    .Range("E" & ActRow + 1 & ":R" & ActRow + ItemQty).Value = Sheet2.Range("y5:AL" & LastFiltRow).Value 'Add Items
    .Range("E" & ActRow + 1 & ":R" & ActRow + 1).EntireColumn.AutoFit
     ActiveWorkbook.Worksheets("ByBrand").ListObjects("TotalIndiesPerformance").Sort _
        .SortFields.Add Key:=Range("J25"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortTextAsNumbers
End With
Exit Sub
NoInv:
MsgBox "There are no Invoices for this customer"
End Sub

Sub Hide_SKU()
With Sheet1
    ActRow = ActiveCell.Row
    LastItemRow = ActiveCell.Offset(1, -1).End(xlDown).Row - 1
    ActiveCell.Value = "+"
    .Range(ActRow + 1 & ":" & LastItemRow).EntireRow.Delete
End With
End Sub
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
your are deleting rows in the table. i would suggest setting the rowheight to 0 and restoring again
 

Guinaba

Board Regular
Joined
Sep 19, 2018
Messages
165
Office Version
  1. 2016
Platform
  1. Windows
Hi @diddi,
Thanks a lot for you suggestion, I've changed to code to RowHeight it is working now :).
There is only one small issue, the drill down doesn't work, when clicking on "+" or "-" in sequence. I have to click in another cell and comeback to that cell with "+" in order to make it work. Would you know why? I am using the selectionChange event below to make that happen.

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("e24:e5000")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "+" Then 'Show SKUs
         Show_SKU
         End
     End If
     If Target.Value = "-" Then 'Hide SKUs
           Hide_SKU
           End
     End If
 End If
 End Sub
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
your event handler is triggered by a change of cell selection
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
change it to any change:

VBA Code:
Private Sub Worksheet_Change(ByVal Target as Range)
 

Guinaba

Board Regular
Joined
Sep 19, 2018
Messages
165
Office Version
  1. 2016
Platform
  1. Windows
your event handler is triggered by a change of cell selection
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
change it to any change:

VBA Code:
Private Sub Worksheet_Change(ByVal Target as Range)
Thanks @diddi! Spott on!!
 

Forum statistics

Threads
1,148,324
Messages
5,746,121
Members
423,993
Latest member
Vighper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top