Macro, Range SelectionClosed

Question

Hello,

I have a macro that chooses dates from a field which is a general download from our information system. It then sorts the dates Ascending, and puts in a True False formula to see if any of the dates are repeated. It then copy pastes values the TRUE and falses so that when you sort, the formula does not relaclulate. It then sorts by TRUE / FALSE to get a resulting list of which dates are not repeated: represented by the FALSEs.  

Resulting Table may look something like this:
23/02/2009	FALSE
24/02/2009	FALSE
25/02/2009	FALSE
26/02/2009	FALSE
27/02/2009	FALSE
28/02/2009	FALSE
01/03/2009	FALSE
23/02/2009	TRUE                 <--(First date that repeats)
23/02/2009	TRUE
23/02/2009	TRUE
23/02/2009	TRUE
23/02/2009	TRUE

I want to then add into the macro code to select the range of only the dates which do not repeat, the FALSE dates. I dont think any of the .SpecialCells methods will work, since I've tried many of them. Any help would be grand.

WutUp WutUp · Accepted Answer

This code will put a thin style border around the date, Column A and "FALSE", Column B.
I don't know what you want to do exactly, but this gives you an idea of how to select the cells with "FALSE".
Hope this helps!


Dim r
Dim intLastRow As Integer
Dim intRow As Integer
r = Range("A65536").End(xlUp).Row
intLastRow = r

For intRow = 1 To intLastRow

Rows(intRow).Select

If Cells(intRow, 2) = "FALSE" Then

    Range(Cells(intRow, 1), Cells(intRow, 2)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    
End If

Next intRow

Range("A1").Select

User1 · Answer

Say that the column containing "True" or "False" is column B.  You could create another adjacent column C with the following formula, say for row 1,  =If(B1="False",1/0,1).  Then fill this formula down.  While the cells in Column C are highlighted, hit Edit > Goto > Special Cells > Errors.  Then you can use the reference:  Selection.Offset(0,-1).Select, and voila you have selected the cells that contain the word "False".

vijay · Answer

it seems you want the list of date where count of date is 1, you may use adodb and do a group by query and then get the list where count=1.

Macro, Range Selection

3 responses

Similar discussions

Subscribe To Our Newsletter!