0

I'm attempting to select all cells from the current active cell up to the last non-blank cell. I'm doing this with the macro recorder right now because I don't know how to write the code yet. What it's recording when I do Ctrl+Shift+Up is

Range(Selection, Selection.End(xlUp)).Select

This is fine, except it's changing the active cell to the top cell instead of the bottom cell. I've tried going from top to bottom, from left to right, and right to left. Down to up and right to left are the only ones where the active cell changes when I run the code. I have a feeling this is because Excel selects ranges in the positive direction by default, but I don't know how to fix the issue.

Stephen Williams
  • 45
  • 1
  • 6
  • 3
    The active cell of a multi-cell selection will always be the top left most cell, to my knowledge there is no way around this. Depending on what it is you're trying to do the solution is to not select anything in code as it is almost always unnecessary. – Warcupine Oct 25 '21 at 13:26
  • 1
    ^^^^^ [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Oct 25 '21 at 13:27
  • New user @dv84 doesn't have enough rep to comment but wants to ask this, so I post ihere as comment: To clarify, do you want the last cell of the range to be the active one after the range is selected? What kind of action do you want to perform after the range is selected? – Christopher Hamkins Oct 25 '21 at 13:46
  • 1
    Skip the `Select`: `Range(Selection, Selection.End(xlUp)).Value = 5`. – BigBen Oct 25 '21 at 14:01
  • To clarify, do you want the last cell of the range to be the active one after the range is selected? What kind of action do you want to perform after the range is selected? – dv84 Oct 25 '21 at 13:39
  • Yes, I want it to function the same way as Crtl + Shift + Up, where the bottom cell stays active. I'm trying to overwrite these cells with a number 5 – Stephen Williams Oct 25 '21 at 13:58

0 Answers0