VBA Delete Row From Table Based On Cell Selection

[t0ny84]

Hi All,

I have a a workbook with two tabs - Main Sheet and Schedule Tab, the events listed in a table on Schedule Tab (example below) show on the Main Sheet within the following cells based on the date selected. Whilst this works a treat I am trying to figure out how to let users delete events from the Schedule Tab. My idea was that if they select the Event Title Cell on the Main Sheet and click a delete button a macro would search the table on Schedule Tab for values matching the Event Title and corresponding date and then delete that row from the table. Does anyone know a way of doing this?

Schedule Tab

Main Sheet

 

[t0ny84]

Well I have gotten to below but the ActiveCell.Offset seems to be not working!

Sub DeleteEvent()
Dim lo As ListObject
  Set lo = Sheet5.ListObjects(1)
  lo.Parent.Activate   
  lo.AutoFilter.ShowAllData
  lo.Range.AutoFilter Field:=2, Criteria1:=ActiveCell.Value
  lo.Range.AutoFilter Field:=1, Criteria2:=ActiveCell.Offset(0,1).Value
  Application.DisplayAlerts = False
  lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
  Application.DisplayAlerts = True
  lo.AutoFilter.ShowAllData
End Sub

 

[t0ny84]

Well I am nearly finished with this code I now just need to make it only delete the cells within the table and not the whole row!!!

Sub DelCalEvent()

Dim testRange As Range 
Dim myRange As Range 
Dim lo As ListObject

' Check to make sure that user only selects cell in certain range.
Set testRange = Range("K6:K39") 'Set the range that macro will run
Set myRange = Selection 'the selection of user
Set lo = Sheet2.ListObjects(1) ' sheet name with table

 lo.AutoFilter.ShowAllData ' removes any filter

If Intersect(testRange, myRange) Is Nothing Then 'Check if the selection is inside the range.

'Selection is NOT inside the range.
   MsgBox "Selection is outside the test range, please ensure you are in the Event List."
    Else
'Selection IS inside the range.
 With lo.Range
.AutoFilter field:=4, Criteria1:="*" & Sheet2.Range("H2") & "*" & ActiveCell.Value
End With

If lo.Range.SpecialCells(xlCellTypeVisible).Count < 1 Then 'check if Autofilter has any results.
'if no results
MsgBox "No Data To Filter"
lo.AutoFilter.ShowAllData
Else
'if results
  Application.DisplayAlerts = False
' Delete Row
lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True

lo.AutoFilter.ShowAllData 'Clear Filter
End If
End If
End Sub

 

[t0ny84]

I found that by using the following code it meant that the additional data in the sheet didn't get deleted.

' clear cells found
lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Clear
' remove filter
lo.AutoFilter.ShowAllData
' delete blank cells
lo.DataBodyRange.SpecialCells(xlCellTypeBlanks).Delete

instead of below

lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
'Clear Filter
lo.AutoFilter.ShowAllData

 

[Fluff]

Cross posted Help Only Deleting Table Rows

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[t0ny84]

Cross posted Help Only Deleting Table Rows

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

My bad, I thought I had included the link with this one and not just on the other thread...

 

[Fluff]