Range.End(xlDown) is selecting values above the reference cell.

Hello,

I am new to VBA and am kind-of teaching myself how to use it. I haven't had any luck finding any info about my particular issue, so any help you could provide would be greatly appreciated.

I have a sheet where cells A1:A50 all have values, but the last cell, A50, is variable, so sometimes it may go down to A100 or whatever. What I want to do is use the amount of cells in the A column as a reference to autofill (right now I'm just testing with selecting the cells) cells in columns where the active cell is, I'm not starting from A1, the valid info actually starts at A8. What I have right now works great except for one problem. If all the cells from A1 through A50 are filled, then it works and selects down to the last cell and I get a selection of equivalent cells in the actively selected column (i.e. D8:D50), but, it any cell above cell A8 is blank, then it no longer selects down to row 50, it now selects up to the first blank cell. If cell A2 is empty, then it will now select D2:D8 instead of D8:D50. How can I get it so ignore blank cells above the starting cell? Isn't xlDown supposed to go down?


Sub SelectOffsetOfColumnA()
'Uses cells in column A as a reference for selection length and selects cells in the same column as the active cell. Starts at A8 and stops at the first empty row in column A.

Dim N As Long
N = Cells(1, 1).End(xlDown).Row
Range("A8:A" & N).Offset(0, ActiveCell.Column - 1).Select

End Sub

xladept,

Thank you. This does work, except for one thing that I forgot to mention in my original post. I need this to select all cells up to the first blank cell. What I am ultimately going to be doing is having one formula doing this for cells A8-A50, then there will be a few blank rows, then there will be another set of data, say, from A60 to A100, which will use the same (but modified for reference cell) formula to cover that block of cells.

I figured it out. What was going on is that in my formula for N, I had N = Cells(1, 1).End(xlDown).Row, which made it start from cell A1 and count down to the next blank cell. I changed the formula to N = Cells(8, 1).End(xlDown).Row, and now it starts from the cell I need it to, A8. Thanks.