PDA

View Full Version : Solved: help speed up this code



Djblois
09-07-2006, 06:26 AM
I was wondering if it is possible to add conditional formating to a whole range at once instead of to one cell and then copy it to the rest. here is the code I am using now:

Range("U2").FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0"
With Range("U2").FormatConditions(1)
.Font.Bold = True
.Font.ColorIndex = 3
End With
Range("U2").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""No Sale"""
With Range("U2").FormatConditions(2)
.Font.Bold = True
.Interior.ColorIndex = 8
End With
Range("U2").FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:=Profit
With Range("U2").FormatConditions(3)
.Font.Bold = True
.Interior.ColorIndex = 6
End With
Range("U2").NumberFormat = "0.00%"

Range("U2").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteFormats

mvidas
09-07-2006, 06:39 AM
Hi djblois,
With Range("U2", Range("U2").End(xlDown))
With .FormatConditions.Add(xlCellValue, xlLess, "0")
.Font.Bold = True
.Font.ColorIndex = 3
End With
With .FormatConditions.Add(xlCellValue, xlEqual, "=""No Sale""")
.Font.Bold = True
.Interior.ColorIndex = 8
End With
With .FormatConditions.Add(xlCellValue, xlLess, Profit)
.Font.Bold = True
.Interior.ColorIndex = 6
End With
.NumberFormat = "0.00%"
End WithThe only time this gets tricky is when you're using cell references in formulas, but for what you have you can just add the formatcondition to the entire range.
Let me know if you need anything else!

Matt

Djblois
09-07-2006, 06:54 AM
Thank you that worked. One more question:

Which runs quicker to find the last row:
row.count

or


range(65536,1).end(xlup).row

mvidas
09-07-2006, 07:01 AM
Personally I prefer to use the Find method looking backwards, it will find the last used row in any column, not just a specified column: Dim LastRow As Long, FND As Range
Set FND = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not FND Is Nothing Then LastRow = FND.RowIf you know for sure the sheet has data in it, you could just use: Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

But if you want to know the last row in a specific column, cells(rows.count,1).end(xlup).rowis what I use.

Matt

Cyberdude
09-07-2006, 10:34 AM
Dim LastRow As Long, FND As Range
Set FND = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not FND Is Nothing Then LastRow = FND.Row Matt, this is an interesting sequence. One wonders what you have if the "IF" statement fails. Do you assume that the sheet is completely empty? Also, is this very time consuming?

mvidas
09-07-2006, 10:58 AM
Sid,

Since LastRow is dim'med as Long, it will be created with a value of 0. If FND turns out to be nothing (the if fails), then LastRow retains the 0 value. Otherwise it gets the row number of the last row with something in it. I put that in just in case it was run on a completely blank sheet.
Not very time consuming at all, quite quick. I think there are a couple KB entries that describe the idea in more detail, if you'd like I could find the links.
http://vbaexpress.com/kb/getarticle.php?kb_id=417

http://vbaexpress.com/kb/getarticle.php?kb_id=418

Matt

Cyberdude
09-08-2006, 07:49 PM
Thanks, Matt. I was just wondering. I'm always amazed by the stuff you guys come up with.

matthewspatrick
09-09-2006, 06:57 AM
Thank you that worked. One more question:

Which runs quicker to find the last row:
Cells(Rows.Count, 1).End(xlUp).Row

or


Cells(65536, 1).End(xlUp).Row


The latter probably works faster, because it has one fewer OLE call. However, we should all get used to using the former, as the latter may end giving you a nasty surprise in Excel 2007, when we exapnd to 1MM and change rows :devil2: