Move Up or Down A Rows Using VBA

[mlo356]

Hi All,

I have continuous (no blanks) data in cells A1 through B10. Currently, I am selecting the cells A1:B1 and using the END function to move to the last row of the dataset which is the range A10:B10. Now I have A1:B10 selected. What I am wanting to do is select the second to last row (A9:B9). At this point I will have A1:B9 selected. SO, I am wanted to move to the end of the data set and then back up one row. How would I do this? I tried offset but it shifts the entire selection and I am only wanting to “back up” a row. I would usually do this with SHIFT+UP ARROW.

Thanks!

[Paul_Hossler]

Try this -- probably other ways, but this I think is the most streight forward



Range("A1:B1").End(xlDown).Offset(-1).Resize(1, 2).Select
MsgBox Selection.Address

[mlo356]

Try this -- probably other ways, but this I think is the most streight forward



Range("A1:B1").End(xlDown).Offset(-1).Resize(1, 2).Select
MsgBox Selection.Address



Thanks for the quick reply. This definitely selects the correct row. I would like A1:B9 to be selected as the result. My apologies if I wasn't clear. Just using Excel manually, it would be:

1. Select A1:B:1
2. Ctrl+Shift+Down to select A1:B10
3. Shift+Up to select A1:B9

Is there a tweak to the formula that you provided that would produce these results?

Thanks Again!!

[SamT]

Range(Range("A1"), Range("B1").End(xlDown).Offset(-1)).Select

[mlo356]

Range(Range("A1"), Range("B1").End(xlDown).Offset(-1)).Select

This worked perfectly. Thank You so much!! I was able to tweak it to fit my situation.