A very basic syntax question about ranges

[Excel Ninja In Training]

The VBA line:

Range(Cells(Application.ActiveCell.SpecialCells(xlLastCell).Row, "E").Address).Select

works perfectly, selecting the cell in the "E" column of the last row in the worksheet with data in it.

So why doesn't the following select the range from E2 to that cell?

Range("E2:Cells(Application.ActiveCell.SpecialCells(xlLastCell).Row, "E").Address").Select

When I try it, I get a syntax error. I must be missing something blindingly obvious.

 

[Sektor]

Range("E2:E" & Cells.SpecialCells(xlLastCell).Row).Select

 

[jeffreybrown]

Seems to me there is a cleaner (more understandable) way to write this, of course always depending on what you are doing.

Sub SelectCells()
    Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row).Select
End Sub

 

[Excel Ninja In Training]

Thank you for the quick replies, Sektor and Jeffery!

Unfortunately, Sektor's method didn't do the trick. I ran it, and got:

Run-time error '1004':
Method 'Range' of object '_Global' failed

Jeffery, your method didn't return any errors, but it didn't do what I needed, either. My worksheet has some completely blank rows in it (the first of which is row 3), but there's more data below them. For some reason, your method ended up just selecting E1 and E2.

What I'm trying to do is select the E column from E2 to the last row with any data at all (at the moment, that's row 346), which is why I'm straining to use "SpecialCells(xlLastCell)" in the code.

 

[njimack]

Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Select

(by the way - you usually don't need to select ranges to perform actions on them)

 

[jeffreybrown]

It works for me like you describe.

I have data in E2, E3 is blank, and E4 has data.

Range("E2:E4") is selected.

Run this on column E. What does the msgbox return?

Sub SelectCells()
    Dim LR As Long
    LR = Range("E" & Rows.Count).End(xlUp).Row
    MsgBox LR
    Range("E2:E" & LR).Select
End Sub

 

[Excel Ninja In Training]

I love how quickly the conversation moves on this board - the challenge is just keeping up.

First, let me say that I was able to make Sektor's method work, with just a slight modification - I used "ActiveCell" instead of "Cells," and that did the trick.

Range("E2:E" & ActiveCell.SpecialCells(xlLastCell).Row).Select

This correctly selected E2:E346.

Jeffrey, I ran your code, and message box returned "1." When I exited the message box, the cells selected were still E1:E2, instead of E2:E346.

Njimack, your code is sadly giving me a syntax error. The error message that pops up in Visual Basic is:

"Compile error: Expected: list separator or )"

As for not needing to select a range - I appreciate the tip. I'm actually using this as a test; now that it's working, I'm going to plug it into a sort function in a bigger piece of code (without selecting the range).

 

[RoryA]

Sektor's code was edited 5 mins after the original post - which version did you try? Jeff's code assumes your last populated row is also populated in column E - I suspect that is not the case.

 

[njimack]

That's what I get for typing code straight onto here instead of the VBE.

Range(Range("E2"), Range("E" & Rows.Count).End(xlUp)).Select

 

[Excel Ninja In Training]

Rory,

I originally tried Sektor's pre-edited version. I tried it again with the edited version, and it worked perfectly.

And you're right, the E column is empty in row 346 - only the A column has data.