0

I need to mimic key shortcuts to select a range that will vary every time. I managed to select a range and need to extend the selection to the entire rows of the selected range. A solution would be to just apply 'shift+spacebar'

Tried resize but I need to select the whole rows.

Sub Clean()
'fill an empty header to apply filter later
Range("A1").Copy
Range("H1").PasteSpecial Paste:=xlPasteValues

'stand on row 3 to then move to 2 with offset, work around
Range("E3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(-1, 0).Select

At this point I got a selection E2:E#, # being the last empty cell before my data starts. I need to extend the selection to the whole rows, which manually would do by pressing Shift+SpaceBar, then delete the entire rows. I couldn't find a way to keysend shift+SpaceBar

Manuel Ramirez
  • 41
  • 1
  • 6
  • 1
    Normally you don't need to `Select` - see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). I think you're looking for [`Range.EntireRow`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.entirerow). – BigBen Jun 26 '19 at 15:28
  • Also FYI, if you just need the values (and not formula, format, etc) you can set ranges equal -- `Range([dest range]).Value = Range([origin range]).Value`, so `Range("H1").Value= Range("A1").Value`. – BruceWayne Jun 26 '19 at 15:32

0 Answers0