Deleting columns based on cell value

[swalk88]

Hi,

I have a spreadsheet with a row of dates. I want to be able to put a date in a cell say "A1" and run a macro to delete all columns with dates newer then the date in A1.

Thanks

 

[dermie_72]

Record a macro.
Highlight the header set filters.
Apply filters to the column with dates, using less than specific date.
Delete the date
stop recording.

Go into the visual basic editor
change the line of code that has the "<date you types" to "<" & range("A1")

then when you re-run the code, it will choose the value that is in A1 as the date variable.

 

[Michael M]

Maybe this

Sub MM1()
Dim lc As Integer, c As Integer
lc = Cells(1, Columns.Count).End(xlToLeft).Column
For c = lc To 2 Step -1
    If Cells(1, c).Value > Cells(1, 1) Then
        Columns(c).Delete
    End If
Next c
End Sub

 

[swalk88]

This works well thanks.

Maybe this

Sub MM1()
Dim lc As Integer, c As Integer
lc = Cells(1, Columns.Count).End(xlToLeft).Column
For c = lc To 2 Step -1
    If Cells(1, c).Value > Cells(1, 1) Then
        Columns(c).Delete
    End If
Next c
End Sub

 

[swalk88]

Hi,

Further to this, how can I set it to start at Column C rather than column A?

Thanks,

 

[Michael M]

This will delete columns from Column "C"

Sub MM1()
Dim lc As Integer, c As Integer
lc = Cells(1, Columns.Count).End(xlToLeft).Column
For c = lc To 3 Step -1
    If Cells(1, c).Value > Cells(1, 1) Then
        Columns(c).Delete
    End If
Next c
End Sub