Simple range set question

[Luke777]

Hi All,

I'm having trouble setting a range. The range should be all A:B where data exists (so A1: last entry in B)

I've tried

 Range("A1:B", Range("A1:B1").End(xlDown)

and a few variations of, but I keep getting "application-define or object-defined error.

What am I doing wrong? It must be something simple but I think I've gone blind to it at this point

 

[Joe4]

One way:

 Range("A1:B" & Cells(Rows.Count,"B").End(xlUp).Row)

Of course, that statement, by itself doesn't do anything. You either need to do something like setting it equal to a range value, or perform some action on it, like selecting it, etc.

 

[Fluff]

Try it like

 Range("A1", Range("B" & rows.count).End(xlup))

 

[Luke777]

Try it like

 Range("A1", Range("B" & rows.count).End(xlup))

Hi,

Went with this solution though I believe both provided work perfectly.

It stops working when I add

Worksheets("sheet1").

to the front of it for some reason though. Not sure what's going on there or why.

 

[Fluff]

You need to to qualify both ranges like

Sheets("Sheet1").Range("A1", Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp))

or

With Sheets("Sheet1")
   .Range("A1", .Range("B" & Rows.Count).End(xlUp))
End With

 

[Luke777]

You need to to qualify both ranges like

Sheets("Sheet1").Range("A1", Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp))

or

With Sheets("Sheet1")
   .Range("A1", .Range("B" & Rows.Count).End(xlUp))
End With

Okay, so my code looks like this

Dim InputRng As Range, ReplaceRng As Range



Set InputRng = Worksheets("Rota").Range("C1", Range("C1").End(xlToRight))

Set ReplaceRng = Worksheets("Data").Range("A1", Range("B" & Rows.Count).End(xlUp))

if I'm on Rota, the second set fails.

if I'm on Data, the first set fails.

I've inserted this as a module, not worksheet level - I don't remember having to switch to the correct sheet first just in order to set a range?

 

[Fluff]

That's because you haven't qualified the ranges as I showed.

 

[Luke777]

That's because you haven't qualified the ranges as I showed.

So it would look like the below?

Set InputRng = With Worksheets("Rota")
                                  .Range("C1", Range("C1").End(xlToRight))
                                  End With

 

[Fluff]

Nope it should be

With Worksheets("Rota")
   Set InputRng = .Range("C1", .Range("C1").End(xlToRight))
End With