Delete Data from sheet

Cartillo
Cartillo used Ask the Experts™
on
Hi Experts,

I would like to request Experts help create a macro to delete the specified data at “Order” sheet based on information given at “Booking” sheet - “Title” (C5), Start/End Date (C7&E7) and Start/End Time (C9&E9). Hope Experts will help me create this feature. I have attached the workbook for Experts perusal.



OrderList.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this macro

Sub delcolumns()
With Sheets("Booking")
    sdat = CVDate(.[c7])
    edat = CVDate(.[e7])
End With
sdcol = Sheets("Order").Range("2:2").Find(sdat).Column
edcol = Sheets("Order").Range("2:2").Find(edat).Column
Sheets("Order").Cells(1, edcol + 1).Resize(1, Columns.Count - edcol).EntireColumn.Delete
If sdcol > 2 Then
Sheets("Order").Cells(1, 2).Resize(1, sdcol - 2).EntireColumn.Delete
End If
End Sub

Author

Commented:
Hi,

The macro not deleting the selected data, instead deleting the whole non-selected column.
Sorry, misread your question. Try this

Sub delcolumns()
With Sheets("Booking")
    sdat = CVDate(.[c7])
    edat = CVDate(.[e7])
End With
sdcol = Sheets("Order").Range("2:2").Find(sdat).Column
edcol = Sheets("Order").Range("2:2").Find(edat).Column
Sheets("Order").Cells(3, sdcol).Resize(144, edcol - sdcol + 1).ClearContents
End Sub

Author

Commented:
Hi,

This macro delete the whole data in the cells instead the selected cell, E.g. Range B5:E43 has multiple data in cell:

TitleA
TitleB
TitleC

When delete TitleA, the whole data were deleted (TitleA,TitleB and TitleC) instead of TitleA.

Please assist
Ok try this

Sub delttls()
With Sheets("Booking")
    sdat = CVDate(.[c7])
    edat = CVDate(.[e7])
    ttl = .[c5]
End With
sdcol = Sheets("Order").Range("2:2").Find(sdat).Column
edcol = Sheets("Order").Range("2:2").Find(edat).Column
For Each cel In Sheets("Order").Cells(3, sdcol).Resize(144, edcol - sdcol + 1)
cel.Value = Replace(cel.Value, ttl, "")
Next cel
End Sub


Author

Commented:
Thanks a lot ssaqibh.
Here's how i'd do it:
Sub DeleteData()

Dim c As Range
Dim WorkingRange As Range
Dim WorkingColumn As String

Dim Title As String
Dim StartDate As Date
Dim EndDate As Date
Dim StartTime As Date
Dim EndTime As Date

StartDate = Sheets("Booking").Range("C7").Value
EndDate = Sheets("Booking").Range("E7").Value
StartTime = Sheets("Booking").Range("C9").Value
EndTime = Sheets("Booking").Range("E9").Value
Title = Sheets("Booking").Range("C5").Value

Set WorkingRange = Sheets("Order").Range("B3", Sheets("B3").SpecialCells(xlCellTypeLastCell))
For Each c In WorkingRange
    WorkingColumn = ColumnLetter(c.Column)
    If Range("A" & c.Row).Value >= StartTime _
        And Range("A" & c.Row).Value <= EndTime _
        And Range(WorkingColumn & "2").Value >= StartDate _
        And Range(WorkingColumn & "2").Value <= EndDate _
        And c.Value = Title Then
        
            c.ClearContents
    End If
    
Next c

End Sub


Public Function ColumnLetter(ColumnNumber As Integer) As String
  If ColumnNumber > 26 Then
    ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
                   Chr(((ColumnNumber - 1) Mod 26) + 65)
  Else
    ColumnLetter = Chr(ColumnNumber + 64)
  End If
End Function

Open in new window

Author

Commented:
Hi NULL_ReferenceException,

Sorry, only realized after I accepting ssaqibh's solution.

If you don't mind, please consider this request:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27243049.html

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial