0

Would appreciate it if someone can provide me with some help.

I have the following VBA code below that does the following

  • looks through the specified range in an Excel worksheet
  • when it finds a cell that matches today's date, it selects that cell

The problem is, this piece of code is looking through 1500 rows to find the cell with today's date, which takes some time. Any thoughts on how I can speed this up? Here's my code:

Dim DateRng As Range, DateCell As Range
Set DateRng = Range("1:1500")
For Each DateCell In DateRng
If DateCell.Value = Date Then DateCell.Select
Next

Thanks in advance!

Cheers

jerry.stockholm
  • 3
  • 1
  • Best not to use a `For Loop` for large ranges if your concerned about speed. The [Find](http://msdn.microsoft.com/en-us/library/office/ff839746(v=office.15).aspx) method is a good place to start. Can you give an example of how your date is formatted, i.e. _mm/dd/yyyy_ ? – Automate This Oct 21 '14 at 04:56
  • possible duplicate of [Iterating 100 cells takes too long](http://stackoverflow.com/questions/5035399/iterating-100-cells-takes-too-long) – Jean-François Corbett Oct 21 '14 at 07:33
  • other duplicates: [How can I optimize this vba loop code?](http://stackoverflow.com/questions/6111276/how-can-i-optimize-this-vba-loop-code) , [VBA Macro to compare all cells of two Excel files](http://stackoverflow.com/questions/5387929/vba-macro-to-compare-all-cells-of-two-excel-files), and there are more... – Jean-François Corbett Oct 21 '14 at 07:37

1 Answers1

0

Your code is looping through 16384 columns by 1500 rows or 24,576,000 cells.

This code limits the search area to a much smaller range and also uses the Range.Find method. I think you will find it much faster.

Sub FindDate()
Dim DateRng As Range, DateCell As Range

Set DateRng = Range("A1:A1500")
With DateRng
    Set DateCell = .Find(Date)
    If Not DateCell Is Nothing Then
        DateCell.Select
    End If
End With
End Sub
Mark Fitzgerald
  • 3,048
  • 3
  • 24
  • 29
  • Hi Mark - thanks for the speedy reply. Your code would work, but the current date is not always located in column A. It can be anywhere from column A to column J (for now). I suppose that's not taken into account in the code you've provided? Thanks again! – jerry.stockholm Oct 21 '14 at 05:28
  • I just changed the range in my code from what it was to A1:J1500 and it's super fast now! Thanks Mark! – jerry.stockholm Oct 21 '14 at 05:35
  • @jerry.stockholm I was just on the way out of the office when I answered so didn't have time to comment that you should modify `Set DateRng = Range("A1:A1500")` to suit your needs. You worked it out though so it's all good in the end. – Mark Fitzgerald Oct 21 '14 at 08:33