Referencing range in another worksheet

G

gtslabs

I am trying to reference all the values in in column A in a different
worksheet.
I get errors using this. Please advise on the correct syntax.

Set AllCells = Worksheets("Data").Range("a2", Cells(Rows.Count,
"a").End(xlUp))
 
J

JLGWhiz

Try this:

Set AllCells = Worksheets("Data").Range("a2", Cells(Rows.Count,
"a").End(xlUp).Address)
 
G

gtslabs

Try this:

Set AllCells = Worksheets("Data").Range("a2", Cells(Rows.Count,
"a").End(xlUp).Address)






- Show quoted text -
Click to expand...

it only got 2 of the 17 cells that currently have data.
 
J

JLGWhiz

If that throws an error then try this:

Set AllCells = Worksheets("Data").Range("a2:" & Cells(Rows.Count,
"a").End(xlUp).Address)
 
G

gtslabs

If that throws an error then try this:

Set AllCells = Worksheets("Data").Range("a2:" & Cells(Rows.Count,
"a").End(xlUp).Address)






- Show quoted text -
Click to expand...

same 2 cells returned
 
D

Dave Peterson

dim AllCells as range
with worksheets("Data")
set allcells = .range("a2",.cells(.rows.count,"A").end(xlup))
end with

msgbox allcells.address
 
G

gtslabs

dim AllCells as range
with worksheets("Data")
  set allcells = .range("a2",.cells(.rows.count,"A").end(xlup))
end with

msgbox allcells.address
Click to expand...

Yes, that worked - Thank you all
 
Top