VBA for overlaying a smaller table Drop Down list

Question

Here is what the following VBA does and works ok: - I have a couple of tables and used named ranges for each of them. - then, on a different tab (where the code is) I have two drop down lists so that the users of my Excel report can select the table (only one at a time) that they want to display data for.

THE PROBLEM for me is that if I select a table from the drop down that has 30 rows all looks fine - table destination cell A2. BUT if then I select a table that has only 10 rows, then the new table with 10 rows is overlaid on the previous 30 row table BUT the 20 rows(remaining from the 30 row table) under the 10 row table are STILL there.

My question is: How should I change the code below so that the 20 rows from the previous table do NOT appear when the 10 rwo table is selected from the drop down list?

let me know if the above doesn't make sense..thanks v much.

My code at the minute is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tableName As String
    Dim tableRange As Range
    Dim TypeOfCosts As String
    Application.EnableEvents = False

    If Range("B1").Text = "Fixed Staff Costs" Then
        TypeOfCosts = "_Fixed_Staff"
    ElseIf Range("B1") = "Variable Staff Costs" Then
        TypeOfCosts = "_Variable_Staff"
    ElseIf Range("B1") = "Production Costs" Then
        TypeOfCosts = "_Production"
    ElseIf Range("B1") = "Infrastructure Costs" Then
        TypeOfCosts = "_Infrastructure"
    ElseIf Range("B1") = "Other Costs" Then
        TypeOfCosts = "_Other"
    Else
        TypeOfCosts = ""
    End If

    tableName = Range("A1").Text & TypeOfCosts & "_Costs"

    On Error Resume Next
    Set tableRange = Application.Range(tableName)
    Debug.Print ThisWorkbook.Names.Count
        If Not (tableRange Is Nothing) And Err = 0 Then
        tableRange.Copy Destination:=Range("A3")
    Else
        Err.Clear
    End If
    On Error GoTo 0
    Application.EnableEvents = True
End Sub

Range("A3").CurrentRegion.ClearContents before you copy the other range, as long long as there's empty space around the existing table (and as long as there's an existing table). It really depends on what the rest of the sheet looks like and whether all your tables are some sort of predictable size/layout etc — Tim Williams
Thanks but where exactly do I need to add your line of code suggested above? — Elena
Are all your tables of similar width/height? Is there anything below them? If nothing else might get cleared, then (eg) Range("A3").Resize(1000,10).ClearContents will clear 100 rows and ten columns starting at A3. If you adjust those numbers so the range will always cover your tables you should be fine. — Tim Williams
sorry to pester but I need to submit this report tomorrow and i've reached a dead end.. similar to the code below from Thomas, this simply deletes the content of the previous table but the format is not deleted eg under the new table 20 rows of blue empty cells. Can you think of anything else? thanks in advance — Elena

Tim Williams Tim Williams · Accepted Answer · 2016-11-13T17:34:21

This is a pretty blunt approach, but without knowing more about your setup:

With Range("A3").Resize(1000,10)
    .ClearContents
    .ClearFormats
End with

VBA for overlaying a smaller table Drop Down list

2 Answers