Grab that row number for a variable in macro

Leah Ahuva

New Member
Joined
Jun 24, 2008
Messages
4
My macro almost does everything I want except hide all the rows starting with the first T row in a particular column.

I am able to find that cell, but I then need to use the the cell number in my Rows select statement. Also when I try to use the variable LastRow, which works elsewhere in my code ( Selection.AutoFill Destination:=Range("E2:E" & LastRow), Type:=xlFillDefault) it does not work:

' Rows("26:LastRow & ").Select
' Rows("26:LastRow").Select
Rows("26:29").Select (Only this hardcode version works)

Ideally I want a variable for the 26 ( the first T row) and the 29.

Here is some of the code:

Sub provision_prepare()

stuff

LastRow = Range("A" & Rows.Count).End(xlUp).Row

stuff

Selection.AutoFill Destination:=Range("E2:E" & LastRow),

Selection.Find(What:="T", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=True).Activate

Somehow use the above selection to find the row number.

More stuff

The commented out statements my code returns with an error message, "Type Mismatch", even though in the little window I see 29 for LastRow (which is variant long but when I made it an integer I still got the same message),

' Rows("26:LastRow & ").Select
' Rows("26:LastRow").Select
Rows("26:29").Select

More Stuff

End Sub

I would be happy to read an article on this, but I cannot seem to find one on this specific subject. Probably I am not doing a meaningful google.

Is there anyone out there that can help me? Our programmer just resigned and they have not hired another yet. I mostly do sql querying for a living not "real" programming.

Thanks.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Try:

Code:
FirstRow = Selection.Find(What:="T", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=True).Row

Then you can use:

Rows(FirstRow & ":" LastRow).Select
 
Upvote 0
Thanks, I did not realize that the Selection returns only a row number and not the letter too. I should have played more with it.

Thanks also for the syntax for the row statement, I never seem to have it straight where to put the quotes.

My code works beautifully now.

Just a tiny correction for others who happen to read this:

Rows(FirstRow & ":" & LastRow).Select

not

Rows(FirstRow & ":" LastRow).Select.

Maybe I will be able to call myself a programmer someday.
 
Upvote 0

Forum statistics

Threads
1,213,581
Messages
6,114,431
Members
448,573
Latest member
BEDE

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
Back
Top