Delete columns based on a cell reference date value

I have a sheet titled 'data' that has source information and row 1 has 
columns H to BG with dates.

On sheet 'Control' is a cell that has a free format cell for dates to be 
entered. I have named this cell 'WCDATA' for ease of another piece of code.

I am tring to create a macro that will locate the date specified in WCDATA 
on the data sheet and delete all columns from H until that which matches 
WCDATA.

Fortunately I have been running on a test file and I have tried several 
options but after considerable hair pulling am seeking assistance as am 
getting close to deadline now.

Any assistance appreciated.

R
0
Utf
11/18/2009 7:59:02 AM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
596 Views

Similar Articles

[PageSpeed] 26

Sub cus()
Dim cell As Range
For i = Range("H1").Column To Worksheets("data").Range
("H1:BG1").Cells.Count
If Cells(1, i).Value <> Range("WCDATA").Value Then
   Cells(1, i).Columns.EntireColumn.Delete
   i = i - 1
Else
   Exit For
End If
Next i

End Sub


On 18 Lis, 08:59, fishy <fi...@discussions.microsoft.com> wrote:
> I have a sheet titled 'data' that has source information and row 1 has
> columns H to BG with dates.
>
> On sheet 'Control' is a cell that has a free format cell for dates to be
> entered. I have named this cell 'WCDATA' for ease of another piece of code.
>
> I am tring to create a macro that will locate the date specified in WCDATA
> on the data sheet and delete all columns from H until that which matches
> WCDATA.
>
> Fortunately I have been running on a test file and I have tried several
> options but after considerable hair pulling am seeking assistance as am
> getting close to deadline now.
>
> Any assistance appreciated.
>
> R

0
Jarek
11/18/2009 8:24:15 AM
corrected

Sub cus()

For i = Range("H1").Column To Worksheets("data").Range
("H1:BG1").Cells.Count
Cells(1, i).Activate
If Cells(1, i).Value <> Range("WCDATA").Value Then
   Cells(1, i).Columns.EntireColumn.Delete
   i = i - 1
Else
   Exit For
End If
Next i

End Sub

On 18 Lis, 08:59, fishy <fi...@discussions.microsoft.com> wrote:
> I have a sheet titled 'data' that has source information and row 1 has
> columns H to BG with dates.
>
> On sheet 'Control' is a cell that has a free format cell for dates to be
> entered. I have named this cell 'WCDATA' for ease of another piece of code.
>
> I am tring to create a macro that will locate the date specified in WCDATA
> on the data sheet and delete all columns from H until that which matches
> WCDATA.
>
> Fortunately I have been running on a test file and I have tried several
> options but after considerable hair pulling am seeking assistance as am
> getting close to deadline now.
>
> Any assistance appreciated.
>
> R

0
Jarek
11/18/2009 8:24:42 AM
You can avoid that looping

Sub Macro8()
Dim varfound As Range
Set varfound = Worksheets("Data").Rows(1).Find(Range("wcdata"))
If Not varfound Is Nothing Then
Worksheets("Data").Range("H1", Cells(1, varfound.Column)).EntireColumn.Delete
End If
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Jarek Kujawa" wrote:

> corrected
> 
> Sub cus()
> 
> For i = Range("H1").Column To Worksheets("data").Range
> ("H1:BG1").Cells.Count
> Cells(1, i).Activate
> If Cells(1, i).Value <> Range("WCDATA").Value Then
>    Cells(1, i).Columns.EntireColumn.Delete
>    i = i - 1
> Else
>    Exit For
> End If
> Next i
> 
> End Sub
> 
> On 18 Lis, 08:59, fishy <fi...@discussions.microsoft.com> wrote:
> > I have a sheet titled 'data' that has source information and row 1 has
> > columns H to BG with dates.
> >
> > On sheet 'Control' is a cell that has a free format cell for dates to be
> > entered. I have named this cell 'WCDATA' for ease of another piece of code.
> >
> > I am tring to create a macro that will locate the date specified in WCDATA
> > on the data sheet and delete all columns from H until that which matches
> > WCDATA.
> >
> > Fortunately I have been running on a test file and I have tried several
> > options but after considerable hair pulling am seeking assistance as am
> > getting close to deadline now.
> >
> > Any assistance appreciated.
> >
> > R
> 
> .
> 
0
Utf
11/18/2009 9:17:02 AM
thks Jacob

On 18 Lis, 10:17, Jacob Skaria <JacobSka...@discussions.microsoft.com>
wrote:
> You can avoid that looping
>
> Sub Macro8()
> Dim varfound As Range
> Set varfound =3D Worksheets("Data").Rows(1).Find(Range("wcdata"))
> If Not varfound Is Nothing Then
> Worksheets("Data").Range("H1", Cells(1, varfound.Column)).EntireColumn.De=
lete
> End If
> End Sub
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
>
> "Jarek Kujawa" wrote:
> > corrected
>
> > Sub cus()
>
> > For i =3D Range("H1").Column To Worksheets("data").Range
> > ("H1:BG1").Cells.Count
> > Cells(1, i).Activate
> > If Cells(1, i).Value <> Range("WCDATA").Value Then
> > =C2=A0 =C2=A0Cells(1, i).Columns.EntireColumn.Delete
> > =C2=A0 =C2=A0i =3D i - 1
> > Else
> > =C2=A0 =C2=A0Exit For
> > End If
> > Next i
>
> > End Sub
>
> > On 18 Lis, 08:59, fishy <fi...@discussions.microsoft.com> wrote:
> > > I have a sheet titled 'data' that has source information and row 1 ha=
s
> > > columns H to BG with dates.
>
> > > On sheet 'Control' is a cell that has a free format cell for dates to=
 be
> > > entered. I have named this cell 'WCDATA' for ease of another piece of=
 code.
>
> > > I am tring to create a macro that will locate the date specified in W=
CDATA
> > > on the data sheet and delete all columns from H until that which matc=
hes
> > > WCDATA.
>
> > > Fortunately I have been running on a test file and I have tried sever=
al
> > > options but after considerable hair pulling am seeking assistance as =
am
> > > getting close to deadline now.
>
> > > Any assistance appreciated.
>
> > > R
>
> > .- Ukryj cytowany tekst -
>
> - Poka=C5=BC cytowany tekst -

0
Jarek
11/19/2009 8:07:14 AM
Reply:

Similar Artilces:

Cell Navigation
how can i force cell navigation in a specific order via the tab key? i have done this before but it has been a while and i can't remember how i did it. thanks for helping. Lock all cells on a sheet (they are by default). Then unlock the cells you want to be able to automatically navigate to, and Protect the sheet, unchecking the option permitting users to select Locked cells. I hope that's what you're looking for. "LaDonna" wrote: > how can i force cell navigation in a specific order via the tab key? i have > done this before but it has been a w...

How to print one header over several columns of data
I want to print one title over a span of columns - what is the command? Format>Cells>Aligment>Horizontal>Center across selection? Gord Dibben MS Excel MVP On Thu, 27 May 2010 14:19:00 -0700, column title print <column title print@discussions.microsoft.com> wrote: >I want to print one title over a span of columns - what is the command? ...

Format only cells containing certain word
Today I was trying to do something - I had a range which contained some cells containing the text string 'Manufacturer:' (my inverted commas) and wanted to format only those cells. Now, I couldn't autofilter, advanced filter or sort because of the sheet structure, and amending it would have taken as long as manually formatting the relevant cells. So I was thinking of how to do it. I was thinking of a conditional format of any cells containing the string. But non-numerical (or non-formula) conditions don't appear to be possible. Could anyone tell me if and how it is po...

Column width: Font characters?!? not inches?
All I can find on Column Width is that the measurement I see displayed is how many characters of the current font will fit across the cell. Is this correct?!? Is there any way to get the column width in inches? Ed You can in MacXL 2004, but not directly in WinXL. With WinXL, you can use VBA. For one way, see Ole Erlandson's site: http://www.erlandsendata.no/english/index.php?d=envbawssetrowcol In article <#N8g#O64EHA.2568@TK2MSFTNGP11.phx.gbl>, "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote: > All I can find on Column Width is that the measurement I se...

Conditional Formating based on other cell values??
Is there a way to apply formatting to one cell based on the conditions of another cell? For example, if A1 is equal to 10 then format C1 to red text. Is there any way to do that. Please let me know either way. Thanks, Dan There are instructions here: http://www.contextures.com/xlCondFormat02.html With your example, select cell C1, and in the conditional formatting dialog box, use the formula: =$A1=10 Dan B wrote: > Is there a way to apply formatting to one cell based on the conditions of > another cell? For example, if A1 is equal to 10 then format C1 to red text. --...

Date to close file
I'm using the date code below to close the file but just figured that if the file is renamed this will fail. Is there code that will close the workbook no matter what the filename to overcome this ? Dim exdate As Date exdate = "07/12/2009" If Date > exdate Then MsgBox ("Sorry this spreadsheet has expired please use latest version") Application.DisplayAlerts = False Workbooks("Master 09 17.50 VAT.XLS").Close Exit Sub End If -- PSM PSM;564544 Wrote: > I'm using the date code below to close the file but just figured tha...

Displaying a cell's value versus it's formula
I have created an amortization schedule. When you click on a cell the toolbar shows the formula I used instead of the dollar value. I don't want anyone to know the formula I used so I want only the value show in the cell. I know you can click on a cell and F2 and then F9 to calculate the value, but I don't want to do that to calculate each individual cell. Is there a faster way to do the whole schedule at once? Thank you, Kris If you lock your cell, and protect the workbook, you can hide the formulas and just see the results. Select your range to lock Format|cells|Protect...

Count occurrences of dates
Column A is a list of dates with the format 01/01/2000, 30/04/2001 etc. How would I count the number of occurrences of dates in column A for the month of say January 2000 How would I count the number of occurrences of dates for the year 2000 Great If you can help. George Gee Hello George, For January 2000 you could use a formula like =SUMPRODUCT(--(TEXT(A1:A100,"mmmyyyy")="Jan2000")) for the whole year =SUMPRODUCT(--(YEAR(A1:A100)=2000)) extend the range as necessary but you can't use the whole column unless you have Excel 2007 Hi Barry Thanks for your re...

change yyyy date to yy
Can I change the date "short" format (yyyy) to "shorter" format (yy)? e.g. 7/7/2005 to 7/7/05 This would be for a range of dates in an existing column, as well as for new dates to be added. Select the col of dates Click Format > Cells > Number tab Under category, choose Custom In Type: box, input: m/d/yy Click OK -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Linda Hart" <Linda Hart@discussions.microsoft.com> wrote in message news:7D172860-C93A-4797-ADB2-AACE5F9E4A25@microsoft.com... > ...

green bullets in cells
What are the green bullet looking items for in the upper right corner of each of my cells. This appeared when I opened a workbook in Excel 2002 that was previously created in Excel 2000. Its not a cell comment. I wanted to know if this green bullet whatever it is can be turned off? Thanks, Bruce Bruce, I believe if signals potential errors in the cells. It can be removed. Go to the Tools menu, choose Options, then the Error Checking tab. There, uncheck the "Enable background error checking" to disable all error checking. -- HTH Bob Phillips ... looking out acros...

How do you match one column's numbers with the 2nd column?
How do you math one column's numbers with the 2nd column? Like if you had: 1 2 2 3 3 6 4 5 6 7 ... and you wanted it sorted like so 2 2 3 3 6 6 1 4 5 7 Any idea how to do that? Thanks, Lee:confused -- lnsykalsk ----------------------------------------------------------------------- lnsykalski's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3403 View this thread: http://www.excelforum.com/showthread.php?threadid=53792 Assuming your data are in columns A and B insert a staging column before column B, with following formu...

Embed Date/Time on existing Video File
I am creating a number of video files that have come from IP Cameras. I need to be able to embed the Date/Time into the file, so that when it plays, you see the current date/time appearing on the screen. I know the start time of the file. I was just wondering if anyone knew how to do this, either directly in VB, or if there is an ActiveX component around to do this. This has to be an automated process, rather than by hand. Thanks -Jerry Not sure if still current but I saw few years (?!) ago DirectShow Editing Services which was part of DirectX and is now at : http...

how do you delete account
I downloaded my checking information and somehow the balance is wrong. I need to delete checking account and then redownload checking activity. I must have done something wrong during the import to Money. I figured out how to delete one item at a time but that would be to time consuming being 100 transactions to delete. Walt The general answer for general cases of people not using Money Essential (MEss) is to go to the Account List, Select the account, and use the Delete... choice on the r-click menu. IF Money discovered this account on its own through the downloaded transaction dat...

Pivot table add one column
How can I add one column in pivot table? When I added a column of Average 100, pivot table created 3 more columns for the first 3 columns( and I don't like it) My data Book Label Price Average A Hard paper 200 100 B Soft 100 100 C Hard paper 200 100 F Plastic 50 100 K Soft 100 100 L Plastic 50 100 M Hard paper 200 100 K soft 100 100 R Soft 100 100 T Plastic 50 100 ------------------------------------------------------------------------------------------- My pivot ...

Refer to a defined name in a formula
Hi Thanks for reading this! I've been scouring the archives but I can't find the answer to this one. Forgive me if it's there somewhere (I'm sure it must be!) I have numerous names defined for individual cells eg. sepr, octmo etc. I am trying to create a formula that will allow me to concatenate two cells eg 'sep' and 'r' and use this to refer to the cell that the name defines. My formula looks something like =25.00*sepr and I would like it to be =25.00*(cell with 'sep')&(cell with 'r') Cheers! -- Andy. Hi Andy try =25*INDIRE...

Julian Date Question (Another)
Thought I had a handle on it, but guess not. This is my code. Me.JulianExpirationDate = Format(DateAdd("yyyy", 5, Now()), "dd/mm/yyyy") ? me.JulianExpirationDate 08/02/2015 Me.JulianExpirationDate = Format(Me.JulianExpirationDate, "yy") & Format(DatePart("y", Me.JulianExpirationDate), "000") ? me.JulianExpirationDate 15214 Shouldn't it be "15039"? What is wrong? Am I missing the whole idea of Julian date. I realize there are many different methods to Julian date calculation, some of which include time...

conditional formatting dates #3
I maintain a "DEMO" spreadsheet that has a return date for the product of mm/dd/yyyy. I would like to have that highlighted 7 days before the product is due back. Any help with a formula would be greatly appreciated!! Also, is there an ability to format so it also emails me a notice? Thanks, Phil Hamm Hi Phil- One Option, there are others- In Format>Conditional Formatting: Cell Value is Less than or equal to =NOW()-7 Set your cell format specs to how you want the date to display. HTH |:>) "Phil" wrote: > I maintain a "DEMO" sp...

Variables and .value
Heres a simple script I've knocked together, largely by trial an error #WhereAmI.ps1 $usr=gci env:username $comp=gci env:computername $IPadd = ([System.Net.Dns]::GetHostAddresses($_) |select $_.ToString) $dom=gci env:userdnsdomain write-host "Formatted with Write-Host:" write-host $(" Username:",$usr.value) -foregroundcolor DarkGreen write-host $(" Computer:",$comp.value) -foregroundcolor red write-host $(" IP Address:", $IPadd) -foregroundcolor gray write-host $(" Domain:",$dom.value) -foregroundcolor DarkYellow w...

ListView header columns
I have a silly question, in a list view in report mode with column headers enabled I am supporting sorting the items in the list view and would like to show the silly little up and down arrows indicating ascending or decending but I don't see any property related to having them displayed. Are they a custom drawn item or am I just missing the obvious? TIA Ron Ron Hall wrote: >I have a silly question, in a list view in report mode with column headers >enabled I am supporting sorting the items in the list view and would like to >show the silly little up and down arrows indicati...

formula does not recognize value
i have a workbook that calculates exp dates. When the date lands on a saturday or sunday, I want it to bump out to the following monday. How can I do this? --- Message posted from http://www.ExcelForum.com/ Make your formula consider the WEEKDAY result of its own calculation and add 1 ot 2 according to that. HTH. Best wishes Harald "Vato Loco >" <<Vato.Loco.16gz0q@excelforum-nospam.com> skrev i melding news:Vato.Loco.16gz0q@excelforum-nospam.com... > i have a workbook that calculates exp dates. When the date lands on a > saturday or sunday, I want it to bump ...

Date formatting won't work
I would like to enter dates in a field using dd/mm/yy, however I want it to display the DAY also ie: ddd dd/mm/yy. I have tried to format the cell in custom formatting however it displays exactly what I have typed, even though the formatting indicates it should read the way I want it to. I have just purchased this copy of Office 2003. Any ideas would be appreciated. It is probably because you are entering a date like 31/12/2005 and excel is trying to read it as mm/dd/yyyy and since there is no month = 31 it converts the whole thing into text string. Always enter as say 31 dec 2004, and w...

Form with cells and TextBoxes...how to TAB to textboxes?
I have a simple sheet that's being used as a questionnaire form. The sheet is Protected, only allowing users to select unlocked cells. Users tab from cell to cell and either type in or select an item from a list (Data/Validation). I also put in some textboxes but I can't tab into them. Is there a way to get the tabbing to work? Thanks, Toby Erkson Oregon, USA WindowsXP, Excel 2003 AFAIK, you cannot tab to a textbox. Tabbing only works between cells. Why would you want to tab to a textbox anyway? "Toby Erkson" <not@necessary.com> wrote in message news:us#QMv9qE...

Columns
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I am working on a translation. <br> I created parallel columns as per: <br><br>I am cutting and pasting my earlier work into this format. However, my footnotes are not moving over. I tried to insert footnotes in this new version, but that option was not available in the text box. <br><br>How do I get footnotes or endnotes for the text in the text boxes? You can have either text boxes or footnotes. Footnotes cannot exist in text boxes because text boxes are not part of the text, they're graphi...

VBA Code to Delete an Email After it is Processed by VBA
The following code is a hybrid from several internet sources. Mostly from this forum. I have an application that starts an email thread and assigns a Task ID like: TID(123) This is the Subject When an email is received with "TID(###)" somewhere in the subject, a native rule then copies it to a subfolder under the CurrentFolder called 'TID'. The following code works to copy the email in msg format to its associated network directory, but the email remains in the subfolders. What I am trying to accomplish is to automatically delete the email after it is pro...

addition with a maximum value #2
Nevermind. Figured it out! =IF(SUM(D10+E10)>20,20,(SUM(D10+E10)+F10) -- djarcadia ----------------------------------------------------------------------- djarcadian's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1587 View this thread: http://www.excelforum.com/showthread.php?threadid=31496 ...