How do I replace all instances of dates with an X?

[dijilator]

I have a spreadsheet with column headers of Skills and row headers of Employees. Where an employee is trained on a given skill, the cell has the training date. All this data is exported from Access. This spreadsheet just needs the fact that they were trained in a given skill, not the training date. To conserve space, I want to change all the training dates to an X and narrow the columns. How would I make a search for all the dates and replace them with an X?

This is in Excel 2007.

 

[JoeMo]

Assuming your sheet consists of constants that are either text or dates, try this on a copy of your sheet.

Sub ReplaceDatesWithX()
On Error Resume Next
ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlNumbers).Value = "X"
On Error GoTo 0
End Sub

 

[jim may]

You have multiple columns with skill dates? Provide such sample column names, please.

 

[galt13]

What space are you trying to conserve? Does the file take up a lot of memory?

 

[jim may]

With...

Excel 2012
	A	B	C	D	E	F
1
2		Column1	My Skills extra	sample	More skills	Another
3		10/28/2012	04/01/2012	01/03/2012	08/08/2010	01/03/2012
4		s	04/01/2012	s	04/01/2012	s
5		10/01/2009	04/01/2012	c	02/18/2014	c
6		10/08/2013	s	09/26/2011	s	09/26/2011
7		c	04/01/2012	c	02/23/2010	c
8		07/07/2012	s	12/31/2013	s	12/31/2013
Sheet1

Run this code...

Sub Foo()
'Figured your Skill Dates would fall within the Range 1/1/2009 (39814) and 1/1/2015 (42005)
Set Rng = Range("B2:F2")
lr = Range("B" & Rows.Count).End(xlUp).Row
For Each C In Rng
    If InStr(1, C, "skills", vbTextCompare) > 0 Then
        For i = 3 To lr
            If Cells(i, C.Column).Value >= 39814 And Cells(i, C.Column).Value <= 42005 Then
            Cells(i, C.Column).Value = "X"
            End If
        Next i
    End If
Next
End Sub

 

[jim may]

Joe,

Your code will change ALL dates in ALL Columns. This might be what the OP wants. I took it that there might be many other columns not related to skills which had dates down the columns.. My Macro modifies only those columns that contain "skill" anywhere in the column header.

Jim

 

[JoeMo]

Joe,

Your code will change ALL dates in ALL Columns. This might be what the OP wants. I took it that there might be many other columns not related to skills which had dates down the columns.. My Macro modifies only those columns that contain "skill" anywhere in the column header.

Jim

Yes, my assumption is the OP has only dates and text and wants all the dates changed to "X" as I stated in my post.

 

[dijilator]

Assuming your sheet consists of constants that are either text or dates, try this on a copy of your sheet.

Sub ReplaceDatesWithX()
On Error Resume Next
ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlNumbers).Value = "X"
On Error GoTo 0
End Sub

Thanks JoeMo,
Perhaps the capitalizations, referring to the headers, in my post were confusing. Nevertheless, you interpreted the question correctly. I've never added VB (would this be called VBE, referring to Excel, I don't know) to a spreadsheet before, so if you could walk me through step by step, beginning after I've opened my spreadsheet, on how to use this code, that would be a big help for me. Thanks.

 

[JoeMo]

Thanks JoeMo,
Perhaps the capitalizations, referring to the headers, in my post were confusing. Nevertheless, you interpreted the question correctly. I've never added VB (would this be called VBE, referring to Excel, I don't know) to a spreadsheet before, so if you could walk me through step by step, beginning after I've opened my spreadsheet, on how to use this code, that would be a big help for me. Thanks.

You are welcome. Here's a step-by-step for installing the code.
To install the code:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the code from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Press Alt+F8 keys to run the code
7. Make sure you have enabled macros whenever you open the file or the code will not run.

 

[shg]

In the alternative, you could select all of the relevant rows or columns, do Ctrl+G (that's Goto), tick Constants and Numbers, then press OK. Then type X in the formula bar, press and hold the Control key, then press Enter.

OR, if the objective is to only to reduce column width, you could just custom-format the cells as "X" (sans quotes), which would preserve the data.