PDA

View Full Version : Solved: xlCellType



Opv
07-18-2010, 06:29 PM
The following code is copying the correct data to the destination range; however, the last line is clearing everything, even my formulas. I thought constants was supposed to clear only the values and leave the formulas in place? How do I need to modify this so that the new row of data removes numbers and text but leaves all formatting and formulas?


Range("Name").Offset(gRows - hRow).EntireRow.copy _
Destination:=Range("Name").Offset(gRows - hRow + 1).EntireRow
Range("Name").Offset(gRows - hRow).EntireRow.SpecialCells(xlCellTypeConstants).Clear

Bob Phillips
07-19-2010, 12:38 AM
Works fine in my test. Post the workbook.

p45cal
07-19-2010, 01:49 AM
Works fine in my test. Post the workbook.

Ditto that, but to retain formats use .clearcontents rather than .clear.

Opv
07-19-2010, 07:03 AM
Thanks guys. I don't know what was going on. I tried it again today and it's working. Although, I was getting some of my formatting and conditional formatting that was lost using the .Clear statement. I changed that to ClearContents and it's doing precisely what I want it to do.

Thanks again,

Opv

Opv
07-21-2010, 07:41 AM
One follow-up question:


Selection.specialcells(xlcelltypeblanks).value = Date


This is working as designed to insert the current date only in the blank cells within the selected range. (Some cells may contain an older date that shouldn't be overwritten.) All is well to this point.

However, I'm curious as to whether there is a way to reverse the action. For example, if the same selection is highlighted, is there a way to clear the contents of only the cells in the selected range that contain the CURRENT date, leaving the older dates intact?

Opv
07-21-2010, 07:43 AM
I guess I could use a loop to accomplish that. If there is a more simple way, I'd be interested.

p45cal
07-21-2010, 08:00 AM
I guess I could use a loop to accomplish that. If there is a more simple way, I'd be interested.you'll probably have to use a loop in vba, but manually, select the cells wherein the dates are so that you restrict the search to that block, Ctrl+f to bring up the Find dialogue box, type in today's date, Find All, Ctrl+A to select all the results, Close the Find dialogue box, todays date cells remain selected, press the delete key on the keyboard.

Recording a macro doing this is not useful.