macro to select row

kendel

Board Regular
Joined
Mar 2, 2010
Messages
133
Hello,

I'd like a macro that will select the row that corresponds to todays date. Dates are in Col A, and todays date is the =today() formula in A3. I'd like the macro to run with a call sub when I open the workbook. The objective is to just select the row, as if I clicked on the row header.

I'm using WIN XP & excel 2007

Thanks,
Kendel
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

rjwebgraphix

Well-known Member
Joined
May 25, 2010
Messages
588
if it is always A3 you're looking for then

Code:
Rows(3).Select

would do the job, however if you're looking at all rows in column A starting at the 2nd Row to the last row you can use.

Code:
Dim DateToday As String
DateToday = Date

Dim i As Long
Dim lr As Long
lr = Range("A" & Rows.count).End(xlUp).row
For i = 2 To lr
    If Cells(i, "A").Value = DateToday Then
        Rows(i).Select
    End If
Next i

although, the fastest way would be to just find the row with the date.

Code:
Dim DateToday As String
DateToday = Date

Dim DateRow As Long
DateRow = Columns("A").Find(DateToday, [A2], , xlWhole, xlByRows, xlPrevious).row

Rows(DateRow).Select

All methods should work. I just immediately went with a loop at first then went Duh! .find would be better and didn't want a good loop to go to waste. :)
 

rjwebgraphix

Well-known Member
Joined
May 25, 2010
Messages
588
Please Pardon my 3rd example above. It was missing something to work with a formula for a date....

this one would work for a .find method.

Code:
Dim DateToday As String
DateToday = Date

Dim DateRow As Long
DateRow = Columns("A").Find(DateToday, [A2], xlValues, xlWhole, xlByRows, xlPrevious).row

Rows(DateRow).Select
 

kendel

Board Regular
Joined
Mar 2, 2010
Messages
133
RJ!!
I couldn't let that loop go to waste!! works great THANKS!!!
 

kendel

Board Regular
Joined
Mar 2, 2010
Messages
133

ADVERTISEMENT

Just want to clarify a little for anyone coming to this

Col A is a list of dates, so I put a button linked to the loop macro in A2 and it selects the row with today's date
just too lazy to scroll to it lol!!

Thanks again RJ!!
 

rjwebgraphix

Well-known Member
Joined
May 25, 2010
Messages
588
RJ!!
I couldn't let that loop go to waste!! works great THANKS!!!

LOL. :) For something this simple, the loop method does work, but the .find method is faster. If there isn't a lot of data you wouldn't notice much of a difference, but if there were 50,000 rows of data, the loop method would take a lot longer than the .find method and it'll become very noticeable.
 

kendel

Board Regular
Joined
Mar 2, 2010
Messages
133
Hey RJ,
I tried running your Find macro but get Run-time error '13': Type Mismatch
I substituted A4, the first cell of the date range, for the [A2] in your code, and when I run the debug, A4 shows 'empty', and the DateToday=0.
It's my guess that the mismatch has something to do with the formating of my date series.
When I run the debug, the 'DateToday' shows as "m/d/yy", text, the date format in the cells are m/d/yy, date format, but it shows in the formula bar as m/d/yyyy, and all attempts at changing that have not been successful.
Can you walk me thru the sequence and help me figure out what's happening?

Thanks,
Kendel
 

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,619
Members
414,081
Latest member
Subaru_Steve

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top