Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

Given a single-cell named range, what expression gives a range of the cell's row?

A worksheet cell has been named" r_Cell".  
What is the expression that will provide a range with r_Cell's entire row?

Dim v_RowRange as Range

Set v_RowRange = ?    '// create a range that is the entire row of range("[r_Cell]")
0
AndresHernando
Asked:
AndresHernando
  • 5
  • 4
1 Solution
 
AlanConsultantCommented:

Hi,

I think this is what you need:

    set v_RowRange = range(r_Cell).entirerow

Alan.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

maybe like

Sub test()

Dim v_RowRange As Range
Dim cRow As Long
cRow = Range("r_cell").Row
Set v_RowRange = Range(cRow & ":" & cRow)
v_RowRange.Select


End Sub

Open in new window


cheers, teylyn
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Ahww! Alan, much better!!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
.... although, you may want to include the quotes around r_Cell, like

    Set v_RowRange = Range("r_Cell").EntireRow

cheers, teylyn
0
 
AlanConsultantCommented:
Yes - Depends on what he means by 'named' cell.

If it is 'named' in VBA something like:

set r_Cell = Range("A1")

then no quotes, but if 'named' in the normal user interface outside of VBA then he would need the quotes.

Alan.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
I work a lot with range names / named formulas / named ranges .... whatever you want to call them. So my antennae flag this as a range name (as in Insert > Name > Define or "Name Manager" in XL 2007 or later).  

That's my gut feel. I could be wrong. It could be a variable, but when I read ...

>> A worksheet cell has been named" r_Cell".  

... my antennae say "named range"!

In any case: as a VBA variable without quote marks, or as a named range with quote marks, your suggestion wins, if I had a say.

cheers, teylyn
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Then again, if it's a VBA variable, you would not wrap it in a Range(), would you? More like ...

dim r_Cell as range
set r_Cell = Range("A1")

    set v_RowRange = r_Cell.entirerow

Open in new window

0
 
AlanConsultantCommented:
Hi Teylyn,

Re-reading the OP, I agree that he probably means a defined name using Insert > Name > Define (or the "Name Manager" in later versions).

In that case, the quotes would be required.

Thanks,

Alan.
0
 
AndresHernandoAuthor Commented:
Thank you both for the info!  --Andres
0
 
AlanConsultantCommented:
Probably should have split the points, but thank you.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now