Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to delete columns
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! -- Hugo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to delete columns
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 --------------- Jacob Skaria "Hugo" wrote: 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! -- Hugo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to delete columns
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! -- Hugo "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Hugo" wrote: 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! -- Hugo |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to delete columns
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 --------------- Jacob Skaria "Hugo" wrote: 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! -- Hugo "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Hugo" wrote: 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! -- Hugo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Columns | Excel Programming | |||
create a macro to delete columns and then border remaining columns | Excel Programming | |||
Delete all Columns with a certain value | Excel Programming | |||
merge text from 2 columns into 1 then delete the old 2 columns | Excel Worksheet Functions | |||
Delete columns | Excel Programming |