Actions without Macro

[HappyChappy]

Hi was wondering can i setup a spread sheet to delete a column of data if one of them coloums is missing say a certain name ie "fred Smith"
and also can it delete whole sheets from the workbook when a certain date is reached

all without the use of a macro.

Regards

Mark

 

[Michael M]

Short answer is no !!
You will need a macro.

 

[HappyChappy]

Cheers
would it need to be a complex Marco to achieve what i'm after

 

[Michael M]

No....the macro recorder could do the deletions, but the rest would have to be written.
Do you need assistance to do that ??

 

[HappyChappy]

i'm afraid i do if that's possible been away from exel for a few years and just starting to use it again it's harder to remember then i thought it would be.

 

[Michael M]

MAybe something like this...checks Sheet1 for "Fred Smith" and deletes Sheet2....you can modify to suit

option compare text
Sub workbook_open()
If Date >= #6/30/2019# Then
    Dim ws As Worksheet, lc As Long, lr As Long, r As Long, n As Long, c As Long
    Set ws = Sheets("Sheet1")
    lr = ws.Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    lc = ws.Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
    n = 0
     With ws
     For c = 1 To lc
            For r = lr To 1 Step -1
                If .Cells(r, c).Value = "Fred Smith" Then
                    n = n + 1
                End If
            Next r
            If n = 0 Then .Columns(c).EntireColumn.Delete
            n = 0
        Next c
    End With
    Application.DisplayAlerts = False
    Sheets("Sheet2").Delete
    Application.DisplayAlerts = True
End If
End Sub

 

[HappyChappy]

thank you so much

 

[Michael M]

Pleasure...