Macro, Range Selection [Closed]

Report
-
 vijay -
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.

3 replies

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
3
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

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".
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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!