When you use Excel and Linked table in powerpivot you want that your user can’t edit column name or cells data, because their change will create problem to your model. But if you block your worksheet, how permit your user to delete or insert new row? With VBA

To insert Data we can use for example  Vba Cells Function. in this case we create different dimension to contain value in specific cell and after that we populate the linked table with stored value, an example with explenation:

Sub InsertValueCells()
‘ declare the variable that we need

Dim nrighe As Integer
Dim cliente As String
Dim Funzione As String
Dim Scenario As String

‘ Using cells fucntion in the first value go row number in the second column number

Funzione = Cells(6, 4)
cliente = Cells(7, 4)
Scenario = “CE Business Review Q3”
Versione = Cells(9, 4)
netnetq4 = Cells(23, 23)
Listingq4 = Cells(26, 23)
Instore4 = Cells(27, 23)

‘ Go to the sheet where is our linked table

Sheets(“Tabella CE Business Review”).Select

‘UNprotect the worksheet

ActiveSheet.Unprotect Password:=”password”

‘Select the linked table

Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects(“NetNet_Previsionale”)

‘ find the last row inserted, the +1 is the distance from le rows a (+1 because i count the column label)
nrighe = tbl.Range.Rows.Count + 1

If Cells(2, 2) = “” Then nrighe = 2

‘ write in the last row in selected column
Cells(nrighe, 2) = cliente
Cells(nrighe, 3) = Funzione
Cells(nrighe, 4) = Scenario
Cells(nrighe, 5) = Versione

Cells(nrighe, 9) = netnetq4
Cells(nrighe, 12) = Listingq4
Cells(nrighe, 15) = Instore4

‘ block worksheet

ActiveSheet.Protect Password:=”Porini_123″, DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True

‘ return to first one worksheet

Sheets(“CE Business review Q3”).Select

‘ Refresh linked table
ActiveWorkbook.Connections(“LinkedTable_ NetNet_Previsionale”).Refresh
MsgBox (“Combinazione Salvata”)

End Sub

This example work if your data aren’t in a table or are in different cells not placed side by side.

To delete rows from linked table you can use this example of Vba sub:

Sub deleterow()
‘ dim to select range
Dim ActSheet As Worksheet
Dim SelRange As Range
Dim iCntr
‘ Unlock your workshhet
ActiveSheet.Unprotect Password:=”PASSWORD”
‘ save the cells ( single o group fo cells) that you have selected before start vba

Set ActSheet = ActiveSheet
Set SelRange = Selection

‘ delete complete each row of selected range

For iCntr = SelRange.row + SelRange.Rows.Count – 1 To SelRange.row Step -1
Rows(iCntr).EntireRow.Delete
Next
‘ block worksheet
ActiveSheet.Protect Password:=”PASSWORD”, DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=Tru

End Sub

To use this Vba macro you have to create a simple button on the same page. The user select one or more cells of row that he wants to delete and click button ( the button have to be connected to the macro)