How to merged rows with dates in Excel

[DCSage]

I have a small macro where I need delete all rows with dates:

Sub allDates()
    Dim i As Long
    
    For i = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
        Debug.Print Cells(i, "a").Value
            If Cells(i, "a") < "6/1/2010" Then
                Cells(i, "a").EntireRow.Delete
                End If
            Next i
End Sub

The macro doesn't seem to always delete dates. Could this be because the worksheet contains blank rows? The row with date is actually merged.

Sample Worksheet Data

--------------------------------------------------------------------------------
Submitted
 
4/1/2010

 
Production              Production Number   
THE DEAL-BDEA-09056     BDEA-09056  
CENTRIC HITS-CH030210   CH030210  
------------------------------------------------------------
Submitted
 
4/6/2010

 
Production                      Production Number   
INFLUENCES-JIFA0708 - TAKE 6    JIFA0708

I tried the macro above with CDate in the following:

If CDATE(Cells(i, "a")) < CDATE("6/1/2010") Then

I received a type mismatch error.

I also tried:

If VALUE(Cells(i, "a")) < VALUE("6/1/2010") Then

and other things within my spreadsheet seemed to be erased.

Is there another alternative?

2. I need for this macro to run in the background when the user pastes the contents into excel worksheet (the data comes from a website). The date will change constantly. How can the date be changed on the fly for each report?

thanks in advance.

 

[SkipVought]

Hi,

MERGE is more of a bane than a blessing.

Check out the MergeArea property in VBA Help.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Are your dates recognised as dates or are they text?

Maybe you can Find the cells containing "Submitted"

Offset to two rows below and unmerge........


Gavin

 

[DCSage]

I need to modify my script so that my user does not have to go through the unmerge and merge. At the most, the users can remove empty columns. Is there a way to extend my macro to actually remove the dates and/or define a variable to remove the date?

 

[SkipVought]

Yes!

Did you check out the MergeArea property?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DCSage]

I am looking into merge area now and trying to figure out how to implement it.