Set a Range to all of the first row

[Kevin McCartney]

I'd like to do two things, set a range to all of row one
and set a second range to all of column A

Please help with the correct code to use. currently I have

Set rgeSearch1 = Application.Worksheets(Z).column(1)
Set rgeSearch1 = Application.Worksheets(Z).rows(1)

Thanks

Best regards
KM

 

[Ron de Bruin]

Try this Kevin

The worksheet tab is named Z

Sub test()
Dim rgeSearch1 As Range
Dim rgeSearch2 As Range
Set rgeSearch1 = ThisWorkbook.Worksheets("Z").Columns(1)
Set rgeSearch2 = ThisWorkbook.Worksheets("Z").Rows(1)
End Sub

 

[Tom Ogilvy]

I assume you would like to use different variable names.

Dim rngSearchRow1 as Range
Dim rngSearchColumn1 as Range
Dim Z as Long
Z = 1
set rgnSearchRow1 = workbooks("Book1.xls").Worksheets(Z).rows(1).Cells

set rngSearchColumn1 = workbooks("Book1.xls").Worksheets(Z).Columns(1).Cells

 

[Tom Ogilvy]

Dim rngSearchRow1 as Range
Dim rngSearchColumn1 as Range

should be

Dim rgeSearchRow1 as Range
Dim rgeSearchColumn1 as Range
Dim Z as Long
Z = 1
set rgeSearchRow1 = workbooks("Book1.xls").Worksheets(Z).rows(1).Cells

set rgeSearchColumn1 = workbooks("Book1.xls").Worksheets(Z).Columns(1).Cells

to be consistent with what you posted and to define the actual variables
uses (my typo).

if Z is the name of the worksheet and not a variable:
Dim rgeSearchRow1 as Range
Dim rgeSearchColumn1 as Range
set rgeSearchRow1 = workbooks("Book1.xls").Worksheets("Z").rows(1).Cells

set rgeSearchColumn1 =
workbooks("Book1.xls").Worksheets("Z").Columns(1).Cells

 

[KM]

Top bannana, cheers buddy

-----Original Message-----
I assume you would like to use different variable names.

Dim rngSearchRow1 as Range
Dim rngSearchColumn1 as Range
Dim Z as Long
Z = 1
set rgnSearchRow1 = workbooks("Book1.xls").Worksheets (Z).rows(1).Cells

set rngSearchColumn1 = workbooks("Book1.xls").Worksheets (Z).Columns(1).Cells

--
Regards,
Tom Ogilvy





.

 

[Alan Beban]

It might behelpful to point out that either Set rng1 = Rows(1) or
Set rng2 = Rows(1).Cells will set the range to all of Row 1, as you can
see from

MsgBox rng1.Address & " " & rng2.Address

But rng1 and rng2 will nevertheless be different, as you can see from

MsgBox rng1(1).Address & " " & rng2(1).Address

rng1 will be a collection of rows, rng2 a collection of cells.

Alan Beban