Deleting columns based on cell value

swalk88

New Member
Joined
Feb 13, 2014
Messages
16
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

Well-known Member
Joined
Sep 4, 2012
Messages
1,540
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,307
Office Version
2013
Platform
Windows
Maybe this
Code:
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

New Member
Joined
Feb 13, 2014
Messages
16
This works well thanks.


Maybe this
Code:
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

New Member
Joined
Feb 13, 2014
Messages
16
Hi,

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

Thanks,
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,307
Office Version
2013
Platform
Windows
This will delete columns from Column "C"
Code:
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
 

Forum statistics

Threads
1,085,543
Messages
5,384,343
Members
401,887
Latest member
Somesh

Some videos you may like

This Week's Hot Topics

Top