variable Row Selection

jgarland

Board Regular
Joined
Nov 6, 2005
Messages
50
Hi All,

I'm trying to find the correct vba syntax to select a row starting from the active cell (variable) out to a named column. The named column location will vary because additional columns will be inserted between it and the active cell.

Example: select active cell A5 (variable) out to my named column (in this case, column G). the resulting selection would be from A5 to G5.

I've tried every combination I can think of, too many to list, plus searched the web, all to no avail. Any suggestions?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
How about
VBA Code:
Sub jgarland()
   Dim Fnd As Range
   
   Set Fnd = Range("1:1").Find("Ward", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then Range(ActiveCell, Cells(ActiveCell.Row, Fnd.Column)).Select
End Sub
Change search term to suit.
 
Upvote 0
Hi, and thanks for responding.

My named column is "lastcolm". I replaced your code "Ward" with that name. Nothing was selected. Am I missing something?
 
Upvote 0
When you say a named column, is that a named range in the name manager, a range that has been declared in VBA, or a value somewhere in the sheet?
 
Upvote 0
Ok, in that case, if you are always select a cell in col A, you can use
VBA Code:
   ActiveCell.Resize(, Range("lastcolumn").Column).Select
 
Upvote 0
That works, but my active cell will not always be in Column A. It will vary depending on where in the code it is called. How do you code it if the active cell was in, for example, column D?
 
Upvote 0
How about
VBA Code:
   ActiveCell.Resize(, Range("Lastcolumn").Column - ActiveCell.Column + 1).Select
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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