VBA to delete columns

[Hugo]

Hi,

I would like to delete columns after a cell of a given value. So for
example, I have a series of cells in a row (A1,B1,C1,D1,E1,F1,G1,etc.) and
each cell is a sequential date. C1 has a value of July 1, 2009, and I would
like my subroutine to delete all cells after this date (to the right of this
cell). Is there a way to lookup the cell values in this row (row 1 in my
example) greater than July 1, 2009 and clear the contents of the entire
columns those cells belong to?

Thanks!

 

[Jacob Skaria]

Try the below...

Sub ClearColumns()
dtTemp = Range("C1")
lngLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
For lngcol = 4 To lngLastCol
If CDate(Cells(1, lngcol)) > dtTemp Then _
Columns(lngcol).ClearContents
Next
End Sub

OR....specify a date

Sub ClearColumns()
dtTemp = datevalue("23-Mar-2009")
lngLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
For lngcol = 1 To lngLastCol
If CDate(Cells(1, lngcol)) > dtTemp Then _
Columns(lngcol).ClearContents
Next
End Sub


If this post helps click Yes

 

[Hugo]

Thanks, Jacob.

That was very helpful, except I now would like to be able to run something
similar, for a row of cells where my date values are formatted in the
following way:

"Jan-09,Feb-09,Mar-09,Q1-09,Apr-09,May-09,June-09,Q2-09,Jul-09,..." (Commas
indicate seperation between cells in a row)

The CDate function gets gummed up when it hits the cells for quarters. Is
there a way to get it to recognize these dates so that the subroutine will
continue until the date I specify?

Thanks!

 

[Jacob Skaria]

Thanks for the response. The below code will consider the Quarter entries as
well. I assume these are text entries in the same format as you
mentioned...Quarter will be considered as the last date of the quater for
example Q1 will be considered as End of march. Try and feedback

Sub ClearColumns()
Dim dtRow As Variant, dtTemp As Date
dtTemp = DateValue("23-Mar-2009")

lngLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
For lngcol = 1 To lngLastCol
dtRow = Cells(1, lngcol)
If UCase(Left(dtRow, 1)) = "Q" Then
dtRow = DateAdd("q", Mid(dtRow, 2, 1), _
"1-Jan-" & Right(dtRow, 2))
dtRow = dtRow - 1
End If
If CDate(dtRow) > dtTemp Then _
Columns(lngcol).ClearContents
Next
End Sub




If this post helps click Yes